Model could not be loaded while executing query

Question

I have a query like this:

$phsql = "
    SELECT s.id AS siteId, s.name 
    FROM site s
    INNER JOIN profiles p ON s.id = p.siteId
    INNER JOIN users_profiles up ON up.profilesId = p.id
        AND p.name = 'admin'
        AND up.usersId = 2
";

Which I converted as following in a model method:

$sites = Site::query()
            ->innerJoin('Profiles', 'Sites.id = Profiles.siteId')
            ->innerJoin('UsersProfiles', 'UsersProfiles.profilesId = Profiles.id')
            ->andWhere('Profiles.name = name')
            ->andWhere('UsersProfiles.usersId = :usersId:', ['userId' => $admin_id])->execute();

On running it gives the error:

Model Profiles could not be loaded

Do note I am running this within Site model.

Update

I tried this:

    $sites = $this->modelsManager->createBuilder() 
    ->from('myApp\Models\Site') 
   ->innerJoin('myApp\Models\Profiles','myApp\Models\Site.id = myApp\Models\Profiles.siteId') 
->andWhere("myApp\Models\Profiles.name = 'admin' ")
 ->where("myApp\Models\UsersProfiles.profilesId = 2")
 ->getQuery()
 ->execute();

And now it gives the error:

Unknown model or alias 'myApp\Models\UsersProfiles' (11), when preparing: SELECT [myApp\Models\Site].* FROM [myApp\Models\Site] INNER JOIN [myApp\Models\Profiles] ON myApp\Models\Site.id = myApp\Models\Profiles.siteId WHERE myApp\Models\UsersProfiles.profilesId = 2

Answer 1

Looking at your code I see two problems:

1) The ->execute() on your second line should throw a parse error?

->innerJoin('Profiles', 'Sites.id = Profiles.siteId')->execute();

2) You have to add namespace to your model, see code below.

A working example of query:

Objects::query()
    ->columns([
        'Models\Objects.id AS objectID',
        'Models\ObjectLocations.id AS locationID',
        'Models\ObjectCategories.category_id AS categoryID',
    ])
    ->innerJoin('Models\ObjectLocations', 'Models\Objects.id = Models\ObjectLocations.object_id')
    ->innerJoin('Models\ObjectCategories', 'Models\Objects.id = Models\ObjectCategories.object_id')
    ->where('Models\Objects.is_active = 1')
    ->andWhere('Models\Objects.id = :id:', ['id' => 2])        
    ->execute();  

You can add a third parameter (alias) to your relation to reduce namespaces and improve your code readability:

->innerJoin('Models\ObjectLocations', 'loc.object_id = obj.id', 'loc');

More info here: https://docs.phalconphp.com/en/latest/api/Phalcon_Mvc_Model_Criteria.html

Also note: using where() and andWhere() adds where clauses to your query. In your first query example the clauses are inside the second join statement, while in your Phalcon query the where clauses are added to the whole query. If you really want those conditions only for the second join, add them to the second join parameter like so:

->innerJoin(
   'Models\ObjectCategories', 
   'Models\Objects.id = Models\ObjectCategories.object_id AND ... AND ... AND ...'
)