is it possible to use reverse mapping in notorm?

63 views
Skip to first unread message

hema malini

unread,
Apr 6, 2015, 3:57:09 AM4/6/15
to not...@googlegroups.com

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

unread,
Apr 6, 2015, 5:28:23 AM4/6/15
to not...@googlegroups.com
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

unread,
Apr 6, 2015, 7:36:03 AM4/6/15
to not...@googlegroups.com
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

unread,
Apr 6, 2015, 7:42:34 AM4/6/15
to not...@googlegroups.com
$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

unread,
Apr 6, 2015, 7:48:23 AM4/6/15
to not...@googlegroups.com
Yes, that was my mistake, forgot to use colon in the select too.
My bad.
Reply all
Reply to author
Forward
Message has been deleted
0 new messages