Trying to use an INNER JOIN in order to speed up page load

1

The problem now is that I need to execute an INNER JOIN between my Users table and my Recipes table because I have too many users. I had too many users already and then after I posted the previous inquiry, 800 new users were added to my users table in my database. An enemy from Germany (BU) registered over 800 email addresses and now my users table is too full. This is what I have in my CategoriesController:

$recipes->join([
    'u' => [
        'table' => 'users',
        'type' => 'INNER',
        'conditions' => 'u.id=recipes.user_id'
    ]
]);

and it worked on localhost but it didn’t work on the server. I got the error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘recipes.user_id’ in ‘on clause’

In my RecipesTable I have:

       $this->hasMany('Users', [
            'foreignKey' => 'user_id',
        ]);
3

Changed the RecipesTable to the following and deleted the cache but still get the same error message.


        $this->belongsTo('Users', [
            'foreignKey' => 'user_id'
        ]);
4

Just to be sure, your recipes table does in fact have a column called user_id? Did you remember to create that on your localhost but forget on the server?

Anyway, I think that with the belongsTo being set on the recipes table, you shouldn’t need to to a manual join. Just make sure you ->contain('Users') when you find recipes, and it should do that for you.

5

I don’t understand how contain(‘Users’) works like a join but this section of my website loads a lot faster now. Thanks, Zuluru.

6

Spoke too soon! I closed my browser and loaded the site again but it was slow like before. contain doesn’t work like a JOIN after all.

8

I deleted the cache and it loaded faster but could be faster.

9

It’s not really any faster. Maybe contain isn’t equivalent to a JOIN after all.

10

I looked at PageSpeed Insights, a web site analysis tool that tells you what is slowing down your website and it said that resources are blocking the first paint of my page and to consider delivering critical JS/CSS inline and deferring all non-critical JS/styles

11

This is what I ended up using and it seems to work:

$recipes = $this->Recipes->find('all') -> contain(['Users' => ['conditions' => ['Recipes.user_id' => 'Users.id']]]);

12

There should be no need for the conditions on the containment, assuming that the relation between those tables is set up correctly. That’s sort of the whole point of containment…