I have two tables with the following structure:
Links --> id, srcId, dstId, kind
Entities --> id, name, x, y
I want to accomplish the following:
SELECT id, name, x, y
FROM Entities
WHERE x BETWEEN :xMin AND :xMax AND y BETWEEN :yMin and :yMax;
entities.id
as either source or destination (the actual query depends on how many entities there are that is how many times where clause is repeated hence ...):SELECT id, srcId, dstId, kind
FROM Links
WHERE srcId = :srcId OR dstId = :dstId ...;
link.srcId
or link.dstId
returned by previous query):SELECT id, name, x, y
FROM Entities
WHERE id = :id ...;
What I then want to return is result of query 2 and 3. Now I already have this implemented in PHP with PDO as outlined above. Meaning I have this 3 step process. However I am wondering if there is a better more efficient way of achieving this?
I heard about PHP multi query, unions, joins, foreign keys however I don't know how and if they are useful in this scenario.
Because I do 3 separate queries there is also a risk of some other process changing the database while in between queries which is not desirable but tolerable at the moment.
Join queries 1 and 2 with a JOIN
SELECT e.id
FROM Entities AS e
JOIN Links AS l ON e.id IN (l.srcId, l.dstId)
WHERE x BETWEEN :xMin AND :xMax AND y BETWEEN :yMin and :yMax;
Then use that as a subquery in query 3:
with selected_links AS (
SELECT l.id, l.srcId, l.dstId
FROM Entities AS e
JOIN Links AS l ON e.id IN (l.srcId, l.dstId)
WHERE x BETWEEN :xMin AND :xMax AND y BETWEEN :yMin and :yMax;
)
SELECT e.id, name, x, y
FROM Entities AS e
JOIN selected_links AS sl ON e.id IN (sl.srcId, sl.dstId)