1

I have to get "relevant" results from a table by joining it on another table and filtering the results on parent id and current id, this all goes fine until your current id is either the first or the last in a result set.

Imagine I have a result set that looks like this: 1,3,6,7,9,23,76 My current id is 3, I need to get the rows with id 1 and 3 back to my application, I managed to do that just fine, now the problem is that the application needs to make a circle.

So using the same result set as above, imagine my current id is 1 I need to get back 3 ( as the next result ) and 76 as my "previous" result.

I also managed to get that to work up to a certain extent, except for when your current id is the maximum, then something weird happens: it gets the next row from the given result set (in the example case if max = 76 then next is 1), but isn't able to retrieve it's previous one.

If I run the query separately, it works fine.

I am using ifnull, since if you don't have a row with an id that is smaller or bigger than the given id, it will return null and should execute the next statement.

Here is the SQL I'm currently using:

SELECT 
CONCAT_WS('/', LOWER(t1.name), LOWER(t2.slug), LOWER(t3.slug)) as slug,
t1.id, t2.id, t3.id
FROM table4 t4
left join table1 t1 on t1.id = t4.t1id
left join table2 t2 on t2.id = t4.t2id
left join table3 t3 on t3.id = t4.t3id
WHERE
t2.id IN (IFNULL((SELECT MAX(id) FROM table2 WHERE parent = 1046 AND id <       1424), (SELECT MAX(id) FROM table2 WHERE parent = 1046)))
OR 
t2.id IN (IFNULL((SELECT MIN(id) FROM table2 WHERE parent = 1046 AND id > 1424), (SELECT MIN(id) FROM table2 WHERE parent = 1046)))

This of course is pseudo code, but the only thing that changes relative to the actual DB is the table/column names.

EDIT: the IFNULLs do actually both return the right result when run separately, and the full query works if the current id is anything but the bigges

I hope you can point me in the right direction !

  • See also "Windowing functions" in MariaDB 10.2. – Rick James May 24 '16 at 23:52
1

Instead of 1424, perhaps you should use t4.t2id. Instead of 1046, if the parent exists in table4 or any of the others, use that link instead of the constant.

You could also use a single IN, and a UNION of both queries.

Finally, if you use a filter condition in WHERE on a LEFT JOINed table, that defeats the purpose of a LEFT JOIN and might result in bugs. Either use JOIN or move the condition to the ON part of that LEFT JOIN. In this case, the condition in the LEFT JOIN was different than the one in WHERE for the same field (t2.id), so it could never work.

SELECT 
CONCAT_WS('/', LOWER(t1.name), LOWER(t2.slug), LOWER(t3.slug)) as slug,
t1.id, t2.id, t3.id
FROM table4 t4
left join table1 t1 on t1.id = t4.t1id
left join table2 t2 on t2.id IN (
    SELECT COALESCE((SELECT MAX(id) FROM table2 WHERE parent = 1046 AND id < t4.t2id),
                    (SELECT MAX(id) FROM table2 WHERE parent = 1046))
    UNION
    SELECT COALESCE((SELECT MIN(id) FROM table2 WHERE parent = 1046 AND id > t4.t2id),
                    (SELECT MIN(id) FROM table2 WHERE parent = 1046)))
left join table3 t3 on t3.id = t4.t3id
where t4.t2id = 1424
  • The problem with this solution is the fact that you use id > t4.t2id this will cause it to get the whole of t4 that matches t2's description, and for some reason, it gets over 900 results, instead of just two, and the ids inside of the IN are not the ids I was supposed to get. 1424, is the ID of the current row, and I only need the previous and next ID from t2, but if that doesn't give me any results on t4, it needs to grab one result further until it matches – FMashiro May 13 '16 at 7:34
  • @FMashiro Of course you'd have to filter by the correct ID at some point, I had left it out because it seemed obvious, but I've updated the answer and included it. The parent field would also be best linked to other tables if it is a related value, rather being specified as a constant 4 times. – Ziggy Crueltyfree Zeitgeister May 13 '16 at 7:46
  • The parent id has to be hardcoded tough, simply because it is a fixed value that I get from a previous query, and finally, just a question, how expensive is this way of doing a left join on a union versus two subqueries in a where clause? (just out of curiosity) and yeah, your updated answer fixed it! – FMashiro May 13 '16 at 9:37
  • @FMashiro in general it is equivalent, but an IN may be faster than an OR condition, but more importantly, less likely to incur in a logic problem, and it's clearer and more maintainable (IMHO) – Ziggy Crueltyfree Zeitgeister May 13 '16 at 10:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.