CODEWITHSUNDEEP
mysqlunionsql-limit
DylanVB
DylanVB

Reputation: 307

MySQL - SELECT equal amount of each in UNION by total LIMIT

In a MySQL database I have 3 tables: customers, projects and tasks. For a search that I am implementing, I want to search through all 3 tables and select found matches. The problem is that I would like to equally limit the amount of results returned by MySQL.

This is an example with the query that I currently have:

SELECT id, title, type
FROM (
    (
    SELECT id, title, 'customer' AS type, MATCH (title) AGAINST ('+test* ' IN BOOLEAN MODE) AS score
    FROM customers
    WHERE MATCH (title) AGAINST ('+test* ' IN BOOLEAN MODE)
    )
    UNION DISTINCT
    (
    SELECT id, title, 'project' AS type, MATCH (title) AGAINST ('+test* ' IN BOOLEAN MODE) AS score
    FROM projects
    WHERE MATCH (title) AGAINST ('+test* ' IN BOOLEAN MODE)
    )
    UNION DISTINCT
    (
    SELECT id, title, 'task' AS type, MATCH (title) AGAINST ('+test* ' IN BOOLEAN MODE) AS score
    FROM tasks
    WHERE MATCH (title) AGAINST ('+test* ' IN BOOLEAN MODE)
    )
) res
LIMIT 6;

In this example, I want to limit the results to 6.

My desired endresult for this example is the following:

1) If all tables have at least 2 results, show 2 results for each.

id     title                      type
20     'First test customer'      'customer'
22     'Test customer 2'          'customer
48     'A project for testing'    'project'
17     'Test Project'             'project'
1      'Task test'                'task'
2      'Second test'              'task'

2) If one table does not have any results, show 3 results for each of the 2 other tables. (If only one table has results, show 6 results for that table.)

id     title                      type
20     'First test customer'      'customer'
22     'Test customer 2'          'customer
56     'Customer test 56'         'customer'
1      'Task test'                'task'
2      'Second test'              'task'
3      'Test task'                'task'

3) If 2 of the tables have more than 2 results, and the third table only has 1 result, show 3 results for one of the tables with enough results, 2 results for the other one of the two and 1 for the table with only 1 result.

id     title                      type
20     'First test customer'      'customer'
48     'A project for testing'    'project'
17     'Test Project'             'project'
34     'Testing project'          'project'
1      'Task test'                'task'
2      'Second test'              'task'

Can anyone please help me with this?

Thanks in advance!

Upvotes: 0

Views: 176

Answers (2)

Mostafa Vatanpour
Mostafa Vatanpour

Reputation: 1408

To have the @kiks73 result in one query without declaring @rankX variables you can add a (SELECT @rankX:=0) AS t to the from clause. like this:

SELECT id, title, type, rank
FROM (
    (

    SELECT @rank1:=@rank1+1 AS rank, id, title, 'customer' AS type, MATCH (title) AGAINST ('+test* ' IN BOOLEAN MODE) AS score
    FROM customers,(SELECT @rank1:=0) AS t
    WHERE MATCH (title) AGAINST ('+test* ' IN BOOLEAN MODE)
    )
    UNION DISTINCT
    (

    SELECT @rank2:=@rank2+1 AS rank, id, title, 'project' AS type, MATCH (title) AGAINST ('+test* ' IN BOOLEAN MODE) AS score
    FROM projects,(SELECT @rank2:=0) AS t
    WHERE MATCH (title) AGAINST ('+test* ' IN BOOLEAN MODE)
    )
    UNION DISTINCT
    (

    SELECT @rank3:=@rank3+1 AS rank, id, title, 'task' AS type, MATCH (title) AGAINST ('+test* ' IN BOOLEAN MODE) AS score
    FROM tasks,(SELECT @rank3:=0) AS t
    WHERE MATCH (title) AGAINST ('+test* ' IN BOOLEAN MODE)
    )
) res
ORDER BY rank
LIMIT 6;

Upvotes: 1

kiks73
kiks73

Reputation: 3768

You might use a row number for every single SELECT and then order the UNION with that calculated field to balance the results obtained from the single queries (I've not tested this code, please take it as a starting point):

SET @rank1=0;
SET @rank2=0;
SET @rank3=0;
SELECT id, title, type, rank
FROM (
    (

    SELECT @rank1:=@rank1+1 AS rank, id, title, 'customer' AS type, MATCH (title) AGAINST ('+test* ' IN BOOLEAN MODE) AS score
    FROM customers
    WHERE MATCH (title) AGAINST ('+test* ' IN BOOLEAN MODE)
    )
    UNION DISTINCT
    (

    SELECT @rank2:=@rank2+1 AS rank, id, title, 'project' AS type, MATCH (title) AGAINST ('+test* ' IN BOOLEAN MODE) AS score
    FROM projects
    WHERE MATCH (title) AGAINST ('+test* ' IN BOOLEAN MODE)
    )
    UNION DISTINCT
    (

    SELECT @rank3:=@rank3+1 AS rank, id, title, 'task' AS type, MATCH (title) AGAINST ('+test* ' IN BOOLEAN MODE) AS score
    FROM tasks
    WHERE MATCH (title) AGAINST ('+test* ' IN BOOLEAN MODE)
    )
) res
ORDER BY rank 
LIMIT 6;

Upvotes: 1

Related Questions