CODEWITHSUNDEEP
mysql
Snake
Snake

Reputation: 121

JOIN two tables, sort by field on second table, no duplicates

My apologies but I cannot get my head around this one (even not after searching and trying out a few things). All I want to do is join two tables and then sort the join descending on the created_at in the article_translations table. However, I need unique entries.

I have two tables:

articles
--------
id
user_id

article_translations
--------
id
article_id (brings this table together with the other one)
locale
title
...
created_at
updated_at

Performing mysql query:

SELECT * from articles
JOIN article_translations as t on t.article_id = articles.id 
ORDER BY t.created_at desc

I get the joined tables with the corresponding related entries.

articles.id t.article_id created_at
1               1           ''
1               1           ''
2               2           ''

When I try no to get rid of the duplicates, in this case of the article with id = 1, I get a nasty error:

Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'blog.t.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

The Desired result would be:

articles.id t.article_id created_at
1               1           ''
2               2           ''

Any help please... Thank You!

Upvotes: 2

Views: 5212

Answers (4)

Booboo
Booboo

Reputation: 44043

Since you do want all the columns:

If you are trying to keep just the article translation with the latest creation date, then assuming the creation dates are unique for a give article's translations, one way would be to create a subquery that computes for each article_translation.article_id the maximum article_translation.created_at column value:

SELECT articles.*, t.* from articles
JOIN article_translations as t on t.article_id = articles.id
JOIN (
   SELECT article_id, max(created_at) as created_at from article_translations
   GROUP BY article_id
) a_t on t.article_id = a_t.article_id and t.created_at = a_t.created_at
ORDER BY t.created_at desc

If the creation dates are not unique, or even if they are, then this should also work:

SELECT articles.*, t.* from articles
JOIN article_translations as t on t.article_id = articles.id
AND t.article_id = (
    SELECT t2.article_id from article_translations t2
    WHERE t2.article_id = t.article_id
    ORDER BY created_date DESC
    LIMIT 1
)
ORDER BY t.created_at DESC

Upvotes: 0

Snake
Snake

Reputation: 121

Thanks forpas for providing the correct answer. I needed this query basically for a Laravel Eloquent model in combination with eager loading. In case anybody cares, that's how the final solution now looks like:

$articles = Article::join('article_translations as at', function ($join) {
                            $join->on('articles.id', '=', 'at.article_id');
                    })
                    ->select('articles.id', 
                    'articles.user_id',DB::raw('MAX(at.created_at) as created_at'))
                    ->groupBy('at.article_id')
                    ->orderBy('created_at', 'desc')
                    ->with('translations')
                    ->get();

Pure SQL

SELECT at.article_id, MAX(at.created_at) as created_at FROM articles as a
INNER JOIN article_translations as at
ON a.id = at.article_id
GROUP BY at.article_id
ORDER BY MAX(created_at) desc

Upvotes: 0

NorthernDev
NorthernDev

Reputation: 339

You are almost there with the query in the question. You just need to add the distinct keyword:

SELECT distinct * from articles
JOIN article_translations as t on t.article_id = articles.id 
ORDER BY t.created_at desc`

Upvotes: 0

forpas
forpas

Reputation: 164069

The only way to get unique rows is if you want the latest (or the earliest?) date for each id, which you can do if you group by a.id, t.article_id and aggregate:

SELECT a.id, t.article_id, MAX(t.created_at) AS created_at 
FROM articles AS a INNER JOIN article_translations AS t 
ON t.article_id = a.id 
GROUP BY a.id, t.article_id
ORDER BY MAX(t.created_at) DESC

If you want all the columns of the 2 tables, first get unique rows from article_translations with NOT EXISTS and then join to articles:

SELECT * 
FROM articles AS a INNER JOIN (
  SELECT t.*
  FROM article_translations t
  WHERE NOT EXISTS (
    SELECT 1 FROM article_translations
    WHERE article_id = t.article_id AND created_at > t.created_at
  )
) AS t 
ON t.article_id = a.id 
ORDER BY t.created_at DESC

This will work if there are not more than 1 rows in article_translations with the same maximum created_at for an article_id.

For MySql 8.0+ you could use ROW_NUMBER():

SELECT t.* FROM (
  SELECT *,  ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY t.created_at DESC) rn 
  FROM articles AS a INNER JOIN article_translations AS t 
  ON t.article_id = a.id 
) AS t
WHERE t.rn = 1

Upvotes: 2

Related Questions