i have categories table and products table. in products table have category_id column type of integer[].

ex: {1,2,3}

.

and i need products list with category relation which categories.id exist products.category_id

i tried in model Product:

public function category()
{
    return $this->belongsTo(Category::class, \DB::raw("ANY(category_id)"), 'id');
}

no get category is null

CodePudding user response：

you should use belongs to many relation. because integer[] type is for saving arrays of ints. try to set it in your model like this:

in your Product(model) you will get this relation method:

public function categories(): BelongsToMany
{
    return $this->belongsToMany(Category::class);
}

And in your Category(model):

public function products(): BelongsToMany
{
    return $this->belongsToMany(Product::class);
}

Refrence

CodePudding user response：

You can try this using laravel query builder

public function category()
{
 return DB::table('products')
 ->join('categories', 'products.category_id', '=', 'categories.id')
 ->get();
}

CodePudding user response：

First of all, I dont think it's possible to do this with the Laravel Relationship Methods.

Second of all, if you are using Postgres, which is a relational Database, you should definitely read up on the fundamentals of database normalization.

I would recommend you have a so called pivot table, that links your products to your categories, which could look something like this:

Disclaimer: You dont need to create a Model for this. Just make a migration with php artisan make:migration create_categories_products_table

categories_products

|         id          |     category_id  |      product_id     |
|---------------------|------------------|---------------------|
|          55         |         1        |          5          |
|          56         |         2        |          5          |
|          57         |         3        |          5          |
|          58         |         1        |          6          |

This table links your tables and this is much more easy to handle than some arrays stored as json.. maybe think about it, it is not that much work to do. You can read upon it on the Laravel Documentation or try searching on google for pivot tables and normalization.

When you have done that:

Now you can just use the Laravel belongsToMany Relationship like so:

// Product.php
public function categories()
{
    return $this->belongsToMany(Category::class, 'categories_products');
}

// Category.php
public function products()
{
    return $this->belongsToMany(Product::class, 'categories_products');
}