Issue with performing a query

I have the following query that I need to implement within phalcon. I have two version of this query, eighter of them is ok.

SELECT trip.*
FROM trips AS trip
LEFT JOIN routes AS route ON trip.id = route.trip_id
LEFT JOIN route_prices AS prices ON route.id = prices.route_id
INNER JOIN (
  SELECT id, date_start, date_end, min(price) as price
  FROM route_prices
  WHERE ( date_start >= UNIX_TIMESTAMP() OR ( date_start <= UNIX_TIMESTAMP() AND date_end >= UNIX_TIMESTAMP() ))
  GROUP BY id
) price ON price.id = prices.id
LEFT JOIN statuses AS status ON trip.status_id = status.id
WHERE status.published = 1 
GROUP BY trip.id
ORDER BY prices.price
SELECT trip.*
FROM trips AS trip
LEFT JOIN routes AS route ON trip.id = route.trip_id
LEFT JOIN route_prices AS prices ON route.id = prices.route_id
INNER JOIN (
  SELECT id, date_start, date_end, min(price) as price
  FROM route_prices
  GROUP BY id
) price ON price.id = prices.id
LEFT JOIN statuses AS status ON trip.status_id = status.id
WHERE status.published = 1
  AND ( prices.date_start >= UNIX_TIMESTAMP() OR ( prices.date_start <= UNIX_TIMESTAMP() AND prices.date_end >= UNIX_TIMESTAMP() ))
GROUP BY trip.id
ORDER BY prices.price

Previously I used the builder class to create te result. Recently there have been changes to the query by adding the inner join. Both queries above produce the right output, but the inner join that was added to the query is giving me trouble. The inner join is used to get only one result back from the prices instead of all of them. This is done so that the orderby is correctly ordering the results.

I have tried both queries above via the query class. That did not work as I end up with an error messge saying:

Syntax error, unexpected token (, near to ' SELECT id, date_start, date_end, MIN( price ) as price FROM Centrio\Models\RoutePrices GROUP BY id ) price ON price.id = prices.id LEFT JOIN Centrio\Models\Statuses AS status ON trip.status_id = status.id WHERE  status.published = 1 AND ( date_start >= UNIX_TIMESTAMP() OR ( date_start <= UNIX_TIMESTAMP() AND date_end >= UNIX_TIMESTAMP() )) GROUP BY trip.id ORDER BY prices.price ', when parsing: SELECT * FROM trips AS Centrio\Models\Trips LEFT JOIN Centrio\Models\Routes AS route ON trip.id = route.trip_id LEFT JOIN Centrio\Models\RoutePrices AS prices ON route.id = prices.route_id INNER JOIN ( SELECT id, date_start, date_end, MIN( price ) as price FROM Centrio\Models\RoutePrices GROUP BY id ) price ON price.id = prices.id LEFT JOIN Centrio\Models\Statuses AS status ON trip.status_id = status.id WHERE  status.published = 1 AND ( date_start >= UNIX_TIMESTAMP() OR ( date_start <= UNIX_TIMESTAMP() AND date_end >= UNIX_TIMESTAMP() )) GROUP BY trip.id ORDER BY prices.price (717)

The error states that the '(' just after the INNER JOIN is not expected. Which is strange as the above quereis work without issues using Navicat'.

The goal: I'm looking for a way to use one of the above queryies in the builder, if possible. Else I'll use the Query class to get my results.

Anyone have an idea how I should implement this in the builder or if not possible in as a raw sql.

If I ever have complex queries like this, I usually just use raw sql. I believe you can access the db connector from a controller with just $this->db



1.9k

Those above queries are just the base. Via a filter on the page in question ($_POST, ofcourse the data is properly sanitized) the where will be extended and where needed more joins are added. So the query will be a bit more complex.

If there is no way other then a RAW sql query then so be it. I'll have a looksee if I get one of the above queries working on that. Thanks for the headsup. I'll let you know if it worked or not.

Oh it's probably possible with the Builder. For me though, when the queries get this complex I start questioning the value of using a framework, when I can just use raw PHP/SQL. Of course, you'll have to do some extra work to get Model objects from this query, but that's a tradeoff I'm often happy with.

Phalcon doesn't support select in joins.



1.9k

I don't mind if it is a model object or not, as long as I have the data I need I'll happy :)