While troubleshooting another issue, I was evaluating the SQL of the view I was working on. This query was built just to demonstrate this issue.

SELECT node.title AS node_title, node.nid AS nid, node.created AS node_created, votingapi_cache_node_meetingtopicvote_count.value AS votingapi_cache_node_meetingtopicvote_count_value, votingapi_cache_node_meetingtopicvote_count.function AS votingapi_cache_node_meetingtopicvote_count_function, 'node' AS field_data_field_showcase_photo_node_entity_type, 'node' AS field_data_body_node_entity_type
FROM 
{node} node
LEFT JOIN {votingapi_cache} votingapi_cache_node_meetingtopicvote_count ON node.nid = votingapi_cache_node_meetingtopicvote_count.entity_id AND (votingapi_cache_node_meetingtopicvote_count.entity_type = 'node' AND votingapi_cache_node_meetingtopicvote_count.tag = 'meetingtopicvote' AND votingapi_cache_node_meetingtopicvote_count.function = 'count')
WHERE (( (node.status = '1') AND (votingapi_cache_node_meetingtopicvote_count.function LIKE 'Sum%' ESCAPE '\\') ))
ORDER BY votingapi_cache_node_meetingtopicvote_count_value DESC, votingapi_cache_node_meetingtopicvote_count_function ASC
LIMIT 12 OFFSET 0

I'd like to direct your attention to the WHERE and ORDER BY clauses:

WHERE (( (node.status = '1') AND (votingapi_cache_node_meetingtopicvote_count.function LIKE 'Sum%' ESCAPE '\\') ))
ORDER BY votingapi_cache_node_meetingtopicvote_count_value DESC, votingapi_cache_node_meetingtopicvote_count_function ASC

notice that in the WHERE clause: votingapi_cache_node_meetingtopicvote_count.function
and in ORDER BY: votingapi_cache_node_meetingtopicvote_count_function

I don't know which is correct count[dot]function or count[underscore]function, but it seems to me that they should be the same.

Comments

torotil’s picture

Status: Active » Closed (works as designed)

Hi,

this works as intended. Also if it were non-functional it would perhaps be a bug in views. Let's take a look at it again:

SELECT … votingapi_cache_node_meetingtopicvote_count.function AS votingapi_cache_node_meetingtopicvote_count_function 
FROM …
WHERE … votingapi_cache_node_meetingtopicvote_count.function LIKE 'Sum%' ESCAPE '\\' …
ORDER BY … votingapi_cache_node_meetingtopicvote_count_function ASC
…

The ORDER BY simply uses the alias defined in the column list of SELECT. That's perfectly fine with SQL.

You might want to filter by function though instead of sorting.

collier.matthew’s picture

Yes, I see the aliases now. Why is the same field being aliased under two different names?

torotil’s picture

That's just how views constructs it's DB queries. It uses aliases for all fields because it also works with more complex expressions I guess.

torotil’s picture

Issue summary: View changes

Correct typo