is it possible to use reverse mapping in notorm?

[hema malini]

i have 3 tables. 

1) products table

id, product_name, seller_id, brand_name

2) products_quantity table

id, products_id, quantity,price_per_bag etc.,

3) product_cat_values table

id,products_id,cat_type, cat_type_value etc.,   (Note: cat_type is a reference from multiple tables like color_types, property_types etc which will have multiple values for a single product )

Now i want to add filter queries. how can i query products_quantity and products_cat_values table from products table using notorm.

if i query using products_quantity table, i can use like this.

$w= "products.status=1";

$w =  "AND ((brand_name = 'Tiger') AND (price_per_bag between 0 and 30))";

$result = $this->orm->products_quantity(array('products.status' => 1))->select("products.*, products_quantity.*")->where("$w");

The above code works well. but in this case, i cant add where conditions for product_cat_values table. For example, if i want to add cat_type=1 (for color) and cat_type_value=2(for blue) from product_cat_values table i cant do it in filter products.

I want to query from products table so that i can add conditions in where clause for all 3 tables. Please help to implement this process.

[Allysson David]

This can be done by using the colon notation, ie:

$this->orm->products()->select('products.*, products_quantity.*')->where('status', 1)->and('(products_quantity:brand_name = ?) AND (products_quantity:price_per_bag between ? and ?)', 'Tiger', 0, 30)->and('product_cat_values:cat_type', 1);

Brief explanation:

$db->table_a()->select('table_a.*')->where('table_b.column_x', 1);

Results in:

SELECT table_a.* FROM table_a LEFT JOIN table_b ON table_a.table_b_id = table_b.id WHERE (table_b.column_x = 1)

While:

$db->table_a()->select('table_a.*')->where('table_b:column_x', 1)

Results in:

SELECT table_a.* FROM table_a LEFT JOIN table_b ON table_a.id = table_b.table_a_id WHERE (table_b.column_x = 1)

In your case, you want to start with the table `products` and use the colon when you need to access columns in tables that are referencing `products`.

TL;DR;

Dot is for when the table is referenced.

Colon is for when the table is referencing.

[hema malini]

Yes i got the result when using colon. 

Thank you very much for immediate response.

i only used base table products.

$this->orm->products(array('products.status'=>1))->where("$w");

The above code works for me.

if i use the select method like,

$this->orm->products(array('products.status'=>1))->select('products.*, products_quantity.*')->where("$w");

it throws error as the query becomes

SELECT products.*, products_quantity.* FROM products LEFT JOIN product_cat_values ON products.id = product_cat_values.products_id LEFT JOIN products_quantity ON products.products_quantity_id = products_quantity.id WHERE (products.status = 1) AND ((product_cat_values.cat_type_value = '2' and product_cat_values.cat_type=1))

because products table does not have products_quantity_id.

Thanks Allysson

[hema malini]

$this->orm->products(array('products.status'=>1))->select('products.*, products_quantity:*')->where("$w");

This works well for selecting data from both table. You saved my time. Thank you.

[Allysson David]

Yes, that was my mistake, forgot to use colon in the select too.

My bad.