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

Are you using namespaces? Why are you doing `->execute();` twice? Is this a copy paste error? — Timothy, Jul 03 '16 at 11:26

score 3 · Accepted Answer · answered Jul 03 '16 at 07:15

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 ...'
)

Now I get message `Unknown model or alias 'Proj\Models\UsersProfile' — Volatil3, Jul 03 '16 at 23:41
You did not join UsersProfiles table `->where("myApp\Models\UsersProfiles.profilesId = 2")` That's why you get unknown error. Perhaps this table **Profiles** has profileID also and you can use it in the where? — Nikolay Mihaylov, Jul 04 '16 at 04:31
` 'Models\Objects.id = Models\ObjectCategories.object_id AND ... AND ... AND ...'` This gives scanning error. — Volatil3, Jul 06 '16 at 15:22
It was just an example, it's not a working code. Can you show what have you tried? Also i would suggest to use QueryBuilder for such tasks (as your last update). — Nikolay Mihaylov, Jul 06 '16 at 17:41

Model could not be loaded while executing query

1 Answers1