Ordering a result set by an alias column

I have 3 models. 1. Casinos [Table in database of casinos] 2. CasinoLanguages [Table in database of all possible languages] 3. RelCasinoLanguages [Table in database that stores the languages each casino offers, has 2 columns, casinoid and language_id]

I can successfully get the result set with the following:

    <?php

        $languages = Rel_Casinos_Languages::find(array(
            "casino_id = :casino_id:", 
            "bind"  => array(
                "casino_id" => $casino->casino_id
            )
        ));

However I wish to order the results alphabetically by the "languagename" in the "CasinoLanguages" model. I have set up the relationships as follows:

    <?php

        // Model: Casino_Languages
        $this->belongsTo("language_id", "\Multiple\Frontend\Models\Rel_Casinos_Languages", "language_id");

        // Model: Rel_Casinos_Languages
        $this->hasOne("language_id", "\Multiple\Frontend\Models\Casino_Languages", "language_id", array(
              'alias' => 'language'
        ));

I then ammended the original find code to look like below:

    <?php

        $languages = Rel_Casinos_Languages::find(array(
            "casino_id = :casino_id:", 
            "order" => "language.language_name",
            "bind"  => array(
                "casino_id" => $casino->casino_id
            )
        ));

However I am now getting an error stating:

Phalcon\Mvc\Model\Exception: Unknown model or alias 'language' (1), when preparing: SELECT [Multiple\Frontend\Models\Rel_Casinos_Languages].* FROM [Multiple\Frontend\Models\Rel_Casinos_Languages] WHERE casino_id = :casino_id: ORDER BY language.language_name

Where am I going wrong? If anyone can help, or provide another method that could work I would be very grateful.

Thanks.



81.1k

In PHQL you have to use classes instead of tables:

<?php

$languages = Rel_Casinos_Languages::find(array(
            "casino_id = :casino_id:", 
            "order" => "Rel_Casinos_Languages.language_name",
            "bind"  => array(
                "casino_id" => $casino->casino_id
            )
));


2.1k

In PHQL you have to use classes instead of tables:

```php <?php

$languages = RelCasinosLanguages::find(array( "casinoid = :casinoid:", "order" => "RelCasinosLanguages.languagename", "bind" => array( "casinoid" => $casino->casino_id ) )); ```

I have just tried that and am still getting this error message: ``` Phalcon\Mvc\Model\Exception: Unknown model or alias 'RelCasinosLanguages' (1), when preparing: SELECT [Multiple\Frontend\Models\RelCasinosLanguages].* FROM [Multiple\Frontend\Models\RelCasinosLanguages] WHERE casinoid = :casinoid: ORDER BY RelCasinosLanguages.language_name ```

I cannot see how it is unknown as it works fine without the ordering.



81.1k

You have to write the complete class name:

$languages = Rel_Casinos_Languages::find(array(
            "casino_id = :casino_id:", 
            "order" => "[Multiple\Frontend\Models\RelCasinosLanguages].language_name",
            "bind"  => array(
                "casino_id" => $casino->casino_id
            )
));

or simply:

$languages = Rel_Casinos_Languages::find(array(
            "casino_id = :casino_id:", 
            "order" => "language_name",
            "bind"  => array(
                "casino_id" => $casino->casino_id
            )
));


2.1k

Still doesnt seem to want to work. I have added the full class name to look like below as the other model contains this column: "order" => "[Multiple\Frontend\Models\Casino_Languages].language_name",

However, I am still getting this error message: Unknown model or alias &#039;Multiple\Frontend\Models\Casino_Languages&#039;

The model does exist as it is being used elsewhere in the controller. Can you confirm if my model relationships are setup correcly? :/



2.1k

Ok, I finally got it working using the query builder instead (Code below). However, if you could please clarify a few things for me, that would be great. ```php $languages = $this->modelsManager->createBuilder() ->from('Multiple\Frontend\Models\RelCasinosLanguages') ->join('Multiple\Frontend\Models\CasinoLanguages', 'Multiple\Frontend\Models\RelCasinosLanguages.languageid = Multiple\Frontend\Models\CasinoLanguages.languageid') ->where('Multiple\Frontend\Models\RelCasinosLanguages.casinoid = :casinoid:', array( "casinoid" => $casino->casinoid )) ->orderBy('Multiple\Frontend\Models\CasinoLanguages.languagename') ->getQuery() ->execute(); ```

What I still do not understand: 1. Why the original find() did not work even with namespaces included? 2. Why I have to use the full namespace in the query builder even though I have this declared at the top? ```php use Multiple\Frontend\Models\CasinoLanguages as CasinoLanguages; use Multiple\Frontend\Models\RelCasinosLanguages as RelCasinosLanguages; ```

Thanks.



60

i also have the problem when i used the PHQL (on 1.3.4)