MySQL - Ordering by a factor obtained by moltiplying a related column by a hABTM

Question

I have 4 tables, photos, tags, photos_tags and visits.

I have to select the first 30 photos ordered by a factor that should be obtained by the sum of the coefficent in the visits table.

I'm trying this query, but it fails saying "every derivated table must have an alias"

    SELECT * FROM (
        SELECT *, (SELECT SUM(count) FROM (SELECT * FROM visits AS Visits WHERE Visits.tag_id IN (
                    SELECT tag_id FROM photos_tags AS PhotosTags WHERE PhotosTags.photo_id = Photo.id
                )))
            ) AS computed FROM photos AS Photo WHERE Photo.id NOT IN (

        ) ORDER BY computed LIMIT 30
    ) ORDER BY RAND()

EDIT: Query I am trying that throws #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AS Table2 ORDER BY computed LIMIT 30 ) AS Table3 ORDER BY RAND()' at line 10

SELECT * FROM (
    SELECT *, (
        SELECT SUM(Table1.count) FROM (
            SELECT * FROM visits AS Visits WHERE Visits.tag_id IN (
                SELECT tag_id FROM photos_tags AS PhotosTags WHERE PhotosTags.photo_id = Photo.id
            )
        ) AS Table1
    ) AS computed FROM photos AS Photo WHERE Photo.id NOT IN (

    ) AS Table2 ORDER BY computed LIMIT 30
) AS Table3 ORDER BY RAND()

EDIT2 Query that throws #1054 - Unknown column 'Photo.id' in 'where clause'

SELECT * FROM (
    SELECT *, (
        SELECT SUM(Table1.count) FROM (
            SELECT * FROM visits AS Visits WHERE Visits.tag_id IN (
                SELECT tag_id FROM photos_tags AS PhotosTags WHERE PhotosTags.photo_id = Photo.id
            )
        ) AS Table1
    ) AS computed FROM photos AS Photo WHERE photos.id NOT IN (
 1,2
    ) ORDER BY computed LIMIT 30
) AS Table3 ORDER BY RAND()

You need to put `AS something` after all the subqueries. It has nothing to do with the ORDER BY clause. — Barmar, Jul 27 '13 at 00:12
possible duplicate of [every derived table must have its own alias](http://stackoverflow.com/questions/1888779/every-derived-table-must-have-its-own-alias) Isn't your first instinct when you get an error to search for that message? If not, it should be. — Barmar, Jul 27 '13 at 00:12
If I put an AS it throws an error near 'FROM photos AS Photo WHERE : `SELECT * FROM ( SELECT *, (SELECT SUM(count) FROM (SELECT * FROM visits AS Visits WHERE Visits.tag_id IN ( SELECT tag_id FROM photos_tags AS PhotosTags WHERE PhotosTags.photo_id = Photo.id )) AS Table1) ) AS computed FROM photos AS Photo WHERE Photo.id NOT IN ( ) ORDER BY computed LIMIT 30 ) ORDER BY RAND()` — Mattia Manzo Manzati, Jul 27 '13 at 00:19
Actually, that wasn't quite accurate. You don't need an alias when a subquery is used in an expression. Only when it's used at the top-level. Put it before `ORDER BY computed` and `ORDER BY RAND`. — Barmar, Jul 27 '13 at 00:20
Same error near 'FROM photos AS Photo WHERE using `SELECT * FROM ( SELECT *, (SELECT SUM(count) FROM (SELECT * FROM visits AS Visits WHERE Visits.tag_id IN ( SELECT tag_id FROM photos_tags AS PhotosTags WHERE PhotosTags.photo_id = Photo.id )) AS Table1) ) AS computed FROM photos AS Photo WHERE Photo.id NOT IN ( ) AS Table2 ORDER BY computed LIMIT 30 ) AS Table3 ORDER BY RAND()` — Mattia Manzo Manzati, Jul 27 '13 at 00:23
Can you show the actual query you're trying? Put it in the question, long codes are unreadable in comments. And please format it and indent it readably. Don't use TAB characters, SO indents them too far. — Barmar, Jul 27 '13 at 00:24

score 1 · Accepted Answer · answered Jul 27 '13 at 00:47

I think this is the query you want:

SELECT *
FROM (SELECT p.*, computed
      FROM Photos AS p
      LEFT JOIN (SELECT pt.photo_id, SUM(count) total
                 FROM photos_tags pt
                 JOIN visits v
                 ON v.tag_id = pt.tag_id
                 GROUP BY photo_id) AS pt
      ON p.id = pt.photo_id
      where p.id NOT IN ( )
      ORDER BY computed
      LIMIT 30) AS t1
ORDER BY RAND()

MySQL - Ordering by a factor obtained by moltiplying a related column by a hABTM

1 Answers1