phpmysqlmulti-query

Selecting form two tables where query is conditional on result from first selection


I have two tables with the following structure:
Links --> id, srcId, dstId, kind
Entities --> id, name, x, y

I want to accomplish the following:

  1. Select all entities within some coordinate range:
SELECT id, name, x, y
FROM Entities
WHERE x BETWEEN :xMin AND :xMax AND y BETWEEN :yMin and :yMax;
  1. Select all links that have any of the previously selected 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 ...;
  1. Select all entities that are either source or destination of any of the previously selected links (here id would be all 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.


Solution

  • 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)