How to fetch 2 types of data with a single mysql query?

Question

Im revising my auto-complete style search script. The site has 2 categories... movies and tv shows. Currently, the auto-complete query string looks like this:

SELECT * FROM movies WHERE mov_title LIKE '%" . $queryString . "%' AND mov_status = 1 AND mov_incomplete = 0  ORDER BY mov_type, mov_title LIMIT 10

The problem with this is if there are more than 10 matches with mov_type = 1 (since it orders it by type first), there wont be any mov_type = 2 records, that would make it into the results.

Is there a way for it to do 5 records from mov_type 1, and then 5 records from mov_type 2. The only way I figured you can do it is, run 2 queries and use UNION to put them together.

I always want to have 10 results if possible... and if a search yields 1 movie (mov_type = 1) and 14 tv shows (mov_type = 2), using UNION this will yield 6 records (since I would do LIMIT 5 on each query), instead of 10 (1 movie, and 9 tv shows).

Any other way I can do this?

Answer 1

This is pretty huge, and would probably perform better if you did some of it in the application layer, but something like this should do it:

select * from ( 
    SELECT * 
    FROM movies 
    WHERE mov_type=1
     and mov_title LIKE '%" . $queryString . "%' 
     AND mov_status = 1 
     AND mov_incomplete = 0  
    LIMIT 5

    UNION ALL

    SELECT * 
    FROM movies 
    WHERE mov_type=2
     and mov_title LIKE '%" . $queryString . "%' 
     AND mov_status = 1 
     AND mov_incomplete = 0  
    LIMIT 5

    UNION ALL 

    SELECT * 
    FROM movies 
    WHERE mov_type=1
     and mov_title LIKE '%" . $queryString . "%' 
     AND mov_status = 1 
     AND mov_incomplete = 0  
     AND id not in (
      SELECT id
      FROM movies 
      WHERE mov_type=1
       and mov_title LIKE '%" . $queryString . "%' 
       AND mov_status = 1 
       AND mov_incomplete = 0  
      LIMIT 5
     )
    LIMIT 5

    UNION ALL 

    SELECT * 
    FROM movies 
    WHERE mov_type=2
     and mov_title LIKE '%" . $queryString . "%' 
     AND mov_status = 1 
     AND mov_incomplete = 0  
     AND id not in (
      SELECT id
      FROM movies 
      WHERE mov_type=2
       and mov_title LIKE '%" . $queryString . "%' 
       AND mov_status = 1 
       AND mov_incomplete = 0  
      LIMIT 5
     )
    LIMIT 5
) a
LIMIT 10

It would be a good idea to move some of this business logic into a view to avoid so much repetition, e.g.:

create view ActiveMovies as
SELECT * 
FROM movies 
WHERE mov_status = 1 
    AND mov_incomplete = 0

Then it would look more like:

select * from (    
    SELECT * 
    FROM ActiveMovies 
    WHERE mov_type=1
        and mov_title LIKE '%" . $queryString . "%' 
    LIMIT 5

    UNION ALL

    SELECT * 
    FROM ActiveMovies 
    WHERE mov_type=2
        and mov_title LIKE '%" . $queryString . "%' 
    LIMIT 5

    UNION ALL 

    SELECT * 
    FROM ActiveMovies 
    WHERE mov_type=1
        and mov_title LIKE '%" . $queryString . "%' 
        AND id not in (
            SELECT id
            FROM ActiveMovies 
            WHERE mov_type=1
                and mov_title LIKE '%" . $queryString . "%' 
            LIMIT 5
        )
    LIMIT 5

    UNION ALL 

    SELECT * 
    FROM ActiveMovies 
    WHERE mov_type=2
        and mov_title LIKE '%" . $queryString . "%' 
        AND id not in (
            SELECT id
            FROM ActiveMovies 
            WHERE mov_type=2
                and mov_title LIKE '%" . $queryString . "%' 
            LIMIT 5
        )
    LIMIT 5
) a
LIMIT 10

Answer 2

Why not:

SELECT * FROM (
    SELECT * FROM movies 
    WHERE mov_title LIKE '%" . $queryString . "%' 
    AND mov_status = 1 
    AND mov_incomplete = 0  
    ORDER BY mov_title
    LIMIT 10
) AS a
ORDER BY a.mov_type, a.mov_title

Subselects make this a much simpler problem.

Answer 3

Just run two queries, one for each category, and have both return 10 items. Then in your application code merge them into a list based on the number or items returned.

Answer 4

something like this should do it:

SET @num:=0; SELECT *, IF (mov_type=1, @num:=@num+2, 11) AS num 
FROM movies 
WHERE mov_title LIKE '%" . $queryString . "%' AND mov_status = 1 AND mov_incomplete = 0 
ORDER BY num, mov_type, mov_title LIMIT 10

edit: Actually it's probably not so efficient, but you can combine it with the union query, should be faster:

SET @num:=0;
SELECT *, IF (mov_type=1, @num:=@num+2, 11) AS num FROM (
    SELECT *
    FROM movies 
    WHERE mov_type=1
        and mov_title LIKE '%" . $queryString . "%' 
        AND mov_status = 1 
        AND mov_incomplete = 0  
    LIMIT 10

    UNION ALL

    SELECT * 
    FROM movies 
    WHERE mov_type=2
        and mov_title LIKE '%" . $queryString . "%' 
        AND mov_status = 1 
        AND mov_incomplete = 0  
    LIMIT 10
) ORDER BY num LIMIT 10;