CODEWITHSUNDEEP
phpmysql
zeeks
zeeks

Reputation: 794

order rows in a table if column value exists in another table

I am currently working on a "search engine" for my website but I am stuck at something.

This is my current query: 

SELECT * FROM posts WHERE title like '$query%' ORDER BY  `title` DESC LIMIT 10

There are over 10k rows in the table posts.

I also have another table: subscribed. Table subscribed is made of 2 columns, post_id and post_title.

When a user searches, I would like to show him 10 posts(related to user search keywords), first the posts he has subscribed(so that are in the subscribed table) and then the others. If he has subscribed to more than 10 posts, then 10 subscribed posts should display. If he has subscribed to less than 10(for example 5), then those 5 subscribed posts should display and 5 others not-subscribed related to his search keywords.

I am looking forward to your answers,

Thank you

Upvotes: 1

Views: 105

Answers (1)

peterm
peterm

Reputation: 92785

First off it's unclear why your data is denormalized by having post_title in subscribed table).

Now, assuming that the query would rely on the title column in the posts table only, you can leverage UNION ALL to achieve the desired result:

(
  SELECT 1 subscribed, p.*
    FROM posts p JOIN subscribed s
      ON p.id = s.post_id
   WHERE p.title LIKE 'keyword%'
   ORDER BY title DESC
   LIMIT 10
)
UNION ALL
(
  SELECT 0 subscribed, p.*
    FROM posts p LEFT JOIN subscribed s
      ON p.id = s.post_id
   WHERE s.post_id IS NULL
     AND p.title LIKE 'keyword%'
   ORDER BY title DESC
   LIMIT 10
)
LIMIT 10

Here is a SQLFiddle demo

Upvotes: 1

Related Questions