0

I would like to order records by created_at of a has_many association and come up to the following raw MySQL query (note: articles_author_associations and article_associations):

SELECT 
  DISTINCT `articles`.* 
  FROM `articles` 
INNER JOIN `articles_author_associations` 
  ON `articles`.`id` = `articles_author_associations`.`article_id`
LEFT OUTER JOIN `article_associations` 
  ON `article_associations`.`article_id` = `articles`.`id` 
  AND `article_associations`.`author_id` = 2 
WHERE `articles_author_associations`.`author_id` = 2  
ORDER BY `article_associations`.`created_at` DESC 
LIMIT 100

The above query returns articles that are not ordered by created_at on the LEFT OUTER JOIN association (the article_associations table).

My question: why? what am I doing wrong?

Backo
  • 18,291
  • 27
  • 103
  • 170
  • 1
    "My question: why? what am I doing wrong?" I advice you to read [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) and provide example data and expected results.. – Raymond Nijland Feb 23 '19 at 19:35
  • @RaymondNijland I just posted a more detailed question [here](https://stackoverflow.com/questions/54847669/more-or-less-complex-ordering-of-a-left-outer-join). Is it enough? – Backo Feb 24 '19 at 00:41
  • A [mcve] should appear as text in your question. You can add a link to an prepped online public execution environment--eg here sqlfiddle.com. Format table initialization code as tables. Clarify via post edits, not comments. Make your posts self-contained. Include, don't, link to, anything necessary to answer. – philipxy Feb 24 '19 at 01:51
  • Is this duplicate of the later question you link to in comments? Please do not repost questions, edit them. – philipxy Feb 24 '19 at 02:10
  • @philipxy The first thing I didn't understand in your writing was "unmatched left table rows extended by NULLs". In my case, with or without LEFT, OUTER, INNER, RIGHT, etc., I would like to return records "simply" ordered on a column/attribute of a table/association as in the question. It would be great if you can point me to useful web resources or, better, answer to the question with some comments. – Backo Feb 24 '19 at 02:12
  • @RaymondNijland No, it does not "still have meaning". No order is guaranteed, it is ignored, any order in what is returned is coincidental, and the manual says this. Allowing order by to *appear* is optional standard SQL functionality. – philipxy Feb 24 '19 at 02:14
  • 1
    You would already gotten better help it you should post ASCII data table as example data and expected which i posted as first comment. – Raymond Nijland Feb 24 '19 at 02:16
  • Left join on returns the rows of the inner join on the same condition union-all the left table rows that didn't get matched in the inner join on extended by nulls. I don't understand the rest of your comment. Putting words in quotes does not make clear the idiosyncratic meaning that is not being written out. Use enough words, sentences & references to parts of examples to be clear. In a post, not a comment. Right now your question does not clearly say what you want for output as a function of input. – philipxy Feb 24 '19 at 02:18
  • Please give a [mcve]--A clear specification plus desired output plus cut & paste & runnable code that is 1. the smallest without your problem and 2. the smallest expansion of that with your problem. – philipxy Feb 24 '19 at 02:39
  • your query is wrong . you can't have join after where clause. i wonder how its work? – Majid Yousefi Feb 24 '19 at 06:16
  • See [How to resolve “ORDER BY clause is not in SELECT list” caused MySQL 5.7 with SELECT DISTINCT and ORDER BY](https://stackoverflow.com/q/36829911) (which is the error message you would get if your sql mode wouldn't suppress it). Essentially, `distinct articles.*` removes any information about `article_associations.created_at`: image you have 10 different values for `created_at` for a specific `article_id` - which one should be used to order the (distincted) result by? If it always has the same value, include it in `distinct` or remove `distinct`. (Sample data would help to be more precise). – Solarflare Feb 24 '19 at 08:49

0 Answers0