Clever Geek Handbook

MySQL: How to work around a temporary table error on this query?

ok I tried my head all day today and can't get this to work, basically I am trying to randomly query 12 photos from a temp table containing all photos that was an active logged in user who has not yet voted on.

Obviously bug # 1137 - Unable to reopen table: "r1" whose temporary table can only be a link.

    CREATE TEMPORARY TABLE unvoted_photos
    SELECT *
    FROM photo
    WHERE photo.photo_uid 
    NOT IN 
    ( SELECT photo_uid
    FROM vote
    WHERE vote.user_uid = '12345' ) ;

    SELECT photo_uid, caption, storage_path
      FROM unvoted_photos AS r1 
      JOIN (SELECT (RAND() * 
                    (SELECT MAX(id)
                    FROM unvoted_photos)) AS sid)
                    AS r2
     WHERE r1.id >= r2.id
     ORDER BY r1.id ASC
     LIMIT 12;

A random select prompt followed this example due to a performance issue. Otherwise it will definitely work.

    SELECT ... FROM photo WHERE photo_uid NOT IN ( voted images subquery ) ORDER BY RAND() LIMIT 12
+3


source to share


1 answer


As per the discussion on the question, here is a way to merge the two that you can run right away. I would rewrite this with JOIN

s, but in this case I put the temp table in a subquery so you can see what happened.



SELECT photo_uid, caption, storage_path
  FROM (
          SELECT photo_uid, caption, storage_path
          FROM photo
          WHERE photo.photo_uid 
          NOT IN 
          ( 
            SELECT photo_uid
            FROM vote
            WHERE vote.user_uid = '12345' 
          )

       ) r1 
  JOIN (SELECT (RAND() * 
                (SELECT MAX(id)
                FROM unvoted_photos)) AS sid)
                AS r2
 WHERE r1.id >= r2.id
 ORDER BY r1.id ASC
 LIMIT 12;
0


source






More articles:


All Articles