Stan Stan - 5 months ago 23
PHP Question

Join to tables and show lowest price of product with Laravel

I'm still extremely new in Laravel and will need some help with this. Currently I have table

products
which has
category_id
column where I save from which category is each product. Another table
category
which hold
category_id
,
category_name
..

Now I want to show all category except category with id=1 on the page and show beside each category link lowest price of product in this category.

So far in my category controller I have only listing all the categories on the page but I don't know how exactly should I construct the query

This is the controller

$categories = Categories::paginate(15);
return View::make('site.index', [
'categories' => $categories
]);
}


I have played with sql query in phpmyadmin and this is the correct query or at least return correct result..

SELECT price
FROM products a
JOIN (
SELECT MIN( price ) AS minprice
FROM products
WHERE category_id =2
)b
ON a.price = b.minprice
AND a.category_id =2

Answer

While it can be done using a raw statement, I don't recommend it, since Laravel provides such functionality out of the box.

You can use Larvel's relations to achieve that.

So you have a table called category which has a manyToMany relations with a products table. As in a category can have many products while at the same time a product can belong to more than one category.

You'll have to create a model for each table you have(I assume you've already done that). After which you'll have to define the relationships for the tables.

Lets assume we have a model called ProductsModel. Lets also assume it looks something like this:

class ProductsModel extends Model
{
    protected $table = 'products';
    protected $fillable = [
       ...
    ];

    //Here we will define the relation between
    //this model and another model
    public function products()
    {
        return $this->hasMany(
            'Namespace\To\Your\CATEGORIES\Model\Goes\Here'
            'related_foregin_key_goes_here'
        );
    }
}

We'll also need another model for the categories.

class CategoriesModel extends Model
{
    protected $table = 'categories';
    protected $fillable = [
       ...
    ];

    //Here we will define the relation between
    //this model and another model
    public function categories()
    {
        return $this->hasMany(
            'Namespace\To\Your\PRODUCTS\Model\Goes\Here',
            'related_foregin_key_goes_here'
        );
    }
}

Once that part is taken care of you can simply do the following:

$products = ProductsModel::where('category_id', '!=', 1)
    ->with('categories')
    ->min('price')
    ->get()
    ->toArray();

Now, I'll be perfectly honest here, my Laravel 4 skills are pretty rusty, but I think it should work just fine.