We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. ORM

findFirst condition as phql breaks app

Hi! I'm writing a cms as a one man hobby project. I started it on phalcon 1.2,, tahn had a half year pause. Now i tyr to revive my project and updated to phalcon 1.3. Earlier this code worked fine:

$trans = ArticlesTranslation::findFirst(["slug = :slug: and language = :language: and language in (select lang_code from Languages where is_active = 1)", "bind" => ["slug" => $slug, ":language:" => $language], "order" => "version desc"]);

But since 1.3 the condition is parsed as phql which doesn't support subquery, so this fails. Is there any good workaround?



3.2k

It's weird that it worked fine because phalcon never supported subqueries.

Anyway, here's a workaround: https://forum.phalcon.io/discussion/1082/sneaking-in-subqueries-into-phql#C4020



3.1k

I guess it worked earlier because condition was parsed as plain sql prepaed statement, but now it's sent through the phql parser. BTW thanks for your answer, I tried that way but no luck. However it gave me few idea to play with...



3.2k
edited Aug '14

Why no luck? The workaround is working for me, your code should look like this:

$trans = ArticlesTranslation::findFirst([
    "slug = :slug: and language = :language: and language in SUBQUERY('select lang_code from Languages where is_active = 1')", 
    "bind" => ["slug" => $slug, ":language:" => $language], 
    "order" => "version desc"]);

The missing piece could be that you can't use Languages as model/table in the subquery because that is a raw query, not parsed by PHQL, so you should use the actual table instead of Languages



3.1k

I don't know why it didn't work, my code looked like this, i have an actual table called languages, but it told something like 'Unexpected token SUBQEURY'. Doesn't matter anyway, I made a db view where article_translation is joined with languages, and filtered by is_active, built a simple model on that view, and used that instead:

$trans = vArticleTransActivelangs::findFirst(["slug = :slug: and language = :language:", 
            "bind" => ["slug" => $slug, "language" => $language], 
            "order" => "version desc"]
        );