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"}]

[thauanvargas]

Hello guys! Any updates in this? I'm also having some issues with join, seems to not be invalidating cache correctly, is it supported ?

This is how I'm populating the database:

        PostComment::create([
            'comment' => $message,
            'post_id' => $taskId,
            'created_by' => $user->id
        ]);

How i'm querying:

        $postComments = Post::select(
            'user.id',
            'user.uuid',
            'user.name',
            'post_comments.created_at',
            'post_comments.comment'
        )->join(
            'post_comments',
            'post_comments.task_id',
            'posts.id'
        )->join(
            'users',
            'user.id',
            'post_comments.created_by'
        )->where('posts.uuid', $uuid)->orderBy('post_comments.created_at', 'desc');

This query doesn't show any updated results.

But, if I change to this:

  $postComments = PostComments::select(
          'user.id',
          'user.uuid',
          'user.name',
          'post_comments.created_at',
          'post_comments.comment'
      )->join(
          'posts',
          'posts.id',
          'post_comments.task_id'
      )->join(
          'user',
          'user.id',
          'post_comments.created_by'
    )->where('posts.uuid', $uuid)->orderBy('post_comments.created_at', 'desc');

it works, but before I updating my laravel-model-caching the first query was working properly if I'm not wrong.

@mikebronner @geniqtech