Need help in fetching data from 3 tables

[Nag]

The table structures follows:
Table products: id,name
Table categories: id, name
Table locations: id, name
Table product_categories: products_id, categories_id
Table product_locations: products_id, locations_id

Each product can be in multiple categories and multiple locations. I need to fetch products from different categories based on a selected single location id with a limit of 10 records.

[Allysson David]

How to get products using conditions based on values from the category and location tables:

echo $db->products()->where('product_locations:locations.name', 'That location');
echo $db->products()->where('product_locations:categories.name', 'That category');

How to limit the number of records to 10 (with pagination):

$page = 1; // please fill this var with any value >= 1
$limit = 10;
$offset = ($page-1)*$limit;
echo $db->products()->limit($limit, $offset); // if the offset is to high (invalid page) you will get zero results

The limit method can be called at any point when you're constructing your query.

You could just append it to the examples given before:

page = 2; // please fill this var with >= 1
$limit = 10;
$offset = ($page-1)*$limit;
echo $db->products()->where('product_locations:locations.name', 'That location')->limit($limit, $offset);
echo $db->products()->where('product_locations:categories.name', 'That category')->limit($limit, $offset);

[Nag]

Thanks for the response Allysson David. 

You have given 2 queries to get products by category and location. But I need it in a single query. I want to fetch location based products from the selected categories in a single query. Please help me in that.

[Allysson David]

You can use ->where() as many times as you want.

Let's suppose that you want the products from category named 'That category' that are in the location named 'That location' (still having the limit of 10 results):

$page = 1; // please fill this var with >= 1

$limit = 10;
$offset = ($page-1)*$limit;

echo $db->products()->where('product_locations:categories.name', 'That category')->where('product_locations:locations.name', 'That location')->limit($limit, $offset);

Multiple calls to ->where() will append the condition using 'AND', thus giving you the desired result.

[Allysson David]

Ah, additionally, if you have an array with multiple categories you can use it as the second argument too instead of 'That category'.

P.S.:

It is important to not send an empty array, though, if you don't care about the category just don't call ->where() for it.

If your array contains multiple categories' ids instead of their names then you should properly change the first argument from 'product_locations:categories.name' to 'product_locations:categories.id' too.

[Allysson David]

P.S.2:

Just to make sure you got it, the $offset (second) param in ->limit() is optional.

If you want only the 10 first records and don't care about any others you can remove these two lines:

$page = 1; // please fill this var with >= 1

...
$offset = ($page-1)*$limit;

And call just ->limit($limit) instead.

[Nag]

It worked for me. Thanks for your help David. I really appreciate your help. 

[Nag]

Now I stuck with another issue:

$page = 1; // please fill this var with >= 1

$limit = 10;
$offset = ($page-1)*$limit;

$proudcts = $db->products()->where('product_categories:categories.id', array(1,2,3,4,5,6))->where('product_locations:locations.id', 6)->limit($limit, $offset);

foreach($products as $product){
    echo $product->product_locations['price'];
}

Here I got a warning says that,  Notice:  Undefined index: product_locations_id. 

Here I am trying to get location based products from the selected categories. Till then it is okay. 

Next I have to display each product price based on the location. The price is stored on the "product_locations" table. When I am trying to fetch the price from the table it is giving me a warning says that "Undefined index: product_locations_id". Because I stored the product id in product_locations table as products_id, but there is no column says "product_location_id" in the "products" table. 

Here how can I get the price from the product_locations table from the query? (or) Do I need to write a separate query to retrieve price from "product_locations" table? 

On Friday, 20 March 2015 01:28:09 UTC+5:30, Allysson David wrote:

[Allysson David]

Easiest solution is to select that column in the query using ->select():

$products = $db->products()->select('products.*, product_locations:price')->where('product_categories:categories.id', array(1,2,3,4,5,6))->where('product_locations:locations.id', 6)->limit($limit, $offset);

Now you can access price using:

foreach($products as $product) {
 echo $product['price'];
}

P.S.:

Remember that you could rename the column using AS in the ->select() if you have multiple columns with the same name in the result.

[Nag]

Thanks David. The issue has been resolved with your solution.