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. Database

Ordering a result set by an alias column

I have 3 models.

  1. Casinos [Table in database of casinos]
  2. Casino_Languages [Table in database of all possible languages]
  3. Rel_Casino_Languages [Table in database that stores the languages each casino offers, has 2 columns, casino_id 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 "language_name" in the "Casino_Languages" 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.



98.9k

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


4.6k
in reply to Phalcon

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

I have just tried that and am still getting this error message:

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

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



98.9k

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


4.6k

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 'Multiple\Frontend\Models\Casino_Languages'

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



4.6k
in reply to Phalcon

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.

        $languages = $this->modelsManager->createBuilder()
            ->from('Multiple\Frontend\Models\Rel_Casinos_Languages')
            ->join('Multiple\Frontend\Models\Casino_Languages', 'Multiple\Frontend\Models\Rel_Casinos_Languages.language_id = Multiple\Frontend\Models\Casino_Languages.language_id')
            ->where('Multiple\Frontend\Models\Rel_Casinos_Languages.casino_id = :casino_id:', array(
                    "casino_id" => $casino->casino_id
                ))
            ->orderBy('Multiple\Frontend\Models\Casino_Languages.language_name')
            ->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? 
    use Multiple\Frontend\Models\Casino_Languages as Casino_Languages;
use Multiple\Frontend\Models\Rel_Casinos_Languages as Rel_Casinos_Languages;

Thanks.



60

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