Is table join supported?

[geniqtech]

Describe the bug
We are using left join as we need to allow sorting of each individual columns returned.
AFAIK this can't be supported using eager loading unless the sorting is done outside of SQL (Which is not very efficient).

The issue here is that results of the query are cached correctly in Redis but if we make changes to countries table, the cached item is not invalidated.

Can I confirm whether this is supported?

Here's a simplified schema for illustration:
Table: countries

+--------------+---------------+
| country_code | name          |
+--------------+---------------+
| US           | United States | 
| JP           | Japan         | 
+--------------+---------------+

Table: products

+----+--------+--------------+
| id | name   | country_code |
+----+--------+--------------+
| 1  | apple  | US           |
| 2  | orange | JP           |
+----+--------+--------------+

Model: Country

class Country extends CachedModel
{
    public function products(): HasMany
    {
        return $this->hasMany(Product::class, 'country_code', 'country_code');
    }
}

Model: Product

class Product extends CachedModel
{
    public function country(): BelongsTo
    {
        return $this->belongsTo(Country::class, 'country_code', 'country_code');
    }
}

Eloquent Query

Products::leftjoin('countries', 'country_code', '=', 'country_code')
	->orderBy('countries.name');

Observations
Cached item is only tagged to "appmodelsproduct:entries" and not "appmodelscountry:entries".
Therefore when changes are made to countries table, the cached item is not invalidated.

Environment

• PHP: 8.2.3
• OS: RockyLinux 9.1
• Laravel: 10.7.1
• Model Caching: 0.13.4

[KimpShreyan]

What if you use
protected $touches = ['Product'];

on Country Model can you confirm
I want to use the in one of my project

[mikebronner]

@geniqtech the key question is how you are making changes to the table. If you make the changes via Eloquent queries, the cache should be invalidated. If you make changes to the table data directly, the cache has no way of knowing you made updates.

[geniqtech]

@mikebronner We are making changes via Eloquent queries. Referring to the example in my use case above, if we make changes to countries table via Eloquent queries, the cached item is not invalidated.

[geniqtech]

@KimpShreyan AFAIK "$touches" only updates the "updated_at" column and is meant for a different purpose.

[mikebronner]

@geniqtech Could you provide the query you are using to make changes?

[geniqtech]

Here's the code snippet:

        // Check before update
        $data = Country::select('countries.*', 'products.name AS product_name')
            ->leftJoin('products', 'products.country_code', 'countries.country_code')->get();
        Log::info($data);

        // Update Product
        $product = Product::find(1);
        $product->name = 'banana';
        $product->save();

        // Check again after update
        $data = Country::select('countries.*', 'products.name AS product_name')
            ->leftJoin('products', 'products.country_code', 'countries.country_code')->get();
        Log::info($data);

Logs Output
local.INFO: [{"country_code":"US","name":"United States","product_name":"apple"},{"country_code":"JP","name":"Japan 1","product_name":"orange"}]
local.INFO: [{"country_code":"US","name":"United States","product_name":"apple"},{"country_code":"JP","name":"Japan 1","product_name":"orange"}]