+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Libraries & Helpers (https://forum.codeigniter.com/forumdisplay.php?fid=22)
+--- Thread: DMZ 1.7.1 (DataMapper OverZealous Edition) (/showthread.php?tid=28550)
DMZ 1.7.1 (DataMapper OverZealous Edition) - El Forum - 08-04-2010
[eluser]WanWizard[/eluser]
If that is the case there is no need for a third table.
What do you intend to do with the Order_album table. Where does that fit into the picture?
DMZ 1.7.1 (DataMapper OverZealous Edition) - El Forum - 08-04-2010
[eluser]patie[/eluser]
[quote author="WanWizard" date="1280954645"]If that is the case there is no need for a third table.
What do you intend to do with the Order_album table. Where does that fit into the picture?[/quote]
i dont want third table but i dont know how i join USER with ORDER
user
id
name
surname
...
order
id
user_id
status
...
order album
id
order_id
album_id
...
album
id
name
price
...
in this context...
Code:
SELECT user.*, order.*, SUM((order_album.price)*order_album.quantity) AS price
FROM order_album
LEFT JOIN order ON order_album.order_id = order.id
LEFT JOIN user ON order.user_id = user.id
GROUP BY order_album.order_id
ORDER BY order.id DESCbecause i need order info, user info and price of order
DMZ 1.7.1 (DataMapper OverZealous Edition) - El Forum - 08-04-2010
[eluser]WanWizard[/eluser]
Read the user guide on deep relations.
You can do
Code:
$obj = new Order_album();
$obj->include_related('order')->include_related('order/user')->order_by('id', 'desc')->group_by('order_id')->get_by_id($order_id);DMZ 1.7.1 (DataMapper OverZealous Edition) - El Forum - 08-04-2010
[eluser]patie[/eluser]
[quote author="WanWizard" date="1280964097"]Read the user guide on deep relations.
You can do
Code:
$obj = new Order_album();
$obj->include_related('order')->include_related('order/user')->order_by('id', 'desc')->group_by('order_id')->get_by_id($order_id);thanks but include_related('order/user') produce :
...
`LEFT OUTER JOIN `user` order_user ON `order_user`.`id` = `order`.`user_id`
...
and my table name is user NOT order_user
what i need is some like this.. if is possible is :
LEFT JOIN user ON order.user_id = user.id
maybe do not understand something im so sorry
DMZ 1.7.1 (DataMapper OverZealous Edition) - El Forum - 08-04-2010
[eluser]WanWizard[/eluser]
DMZ determines the names used in the query from the names of the tables, and the relationships defined. The use of the order_user table indicates DMZ sees a many_to_many relationship between user and order.
You need these relations to be defined:
Order must have a has_one to User, User must have a has_many to order.
Order must have a has_many (or a has_one) to Order_album, Order_album must have a has_one to Order
Order_album must have a has_one to Album, Album must have a has_many to Order_album.
DMZ 1.7.1 (DataMapper OverZealous Edition) - El Forum - 08-04-2010
[eluser]patie[/eluser]
[quote author="WanWizard" date="1280965710"]DMZ determines the names used in the query from the names of the tables, and the relationships defined. The use of the order_user table indicates DMZ sees a many_to_many relationship between user and order.
You need these relations to be defined:
Order must have a has_one to User, User must have a has_many to order.
Order must have a has_many (or a has_one) to Order_album, Order_album must have a has_one to Order
Order_album must have a has_one to Album, Album must have a has_many to Order_album.[/quote]
yes i have exactly what you write
and sql is :
`LEFT OUTER JOIN `user` order_user ON `order_user`.`id` = `order`.`user_id`
must i have NEW table for relation user <> order ??
I would like F.K. order.user_id = user.id
isnt possible ??
thanks
DMZ 1.7.1 (DataMapper OverZealous Edition) - El Forum - 08-04-2010
[eluser]WanWizard[/eluser]
If it's a one-to-many, that shouldn't be necessary, your FK should be ok like this.
You are doing something wrong somewhere, I just created a test database with your 4 tables, created the models, and ran the query I posted before.
Result:
Code:
SELECT `order_albums`.*, `orders`.`user_id` AS order_user_id, `orders`.`status` AS order_status, `order_users`.`id` AS order_user_id, `order_users`.`name` AS order_user_name
FROM (`order_albums`)
LEFT OUTER JOIN `orders` orders ON `orders`.`id` = `order_albums`.`order_id`
LEFT OUTER JOIN `users` order_users ON `order_users`.`id` = `orders`.`user_id`
GROUP BY `order_albums`.`order_id` ORDER BY `order_albums`.`id` DESCSo, I still think there's something wrong in the definition of your relationships.
Also, note the naming conventions. The model is called "User", the foreign key is called "user_id", the table is called "Users". Note the plural form!
DMZ 1.7.1 (DataMapper OverZealous Edition) - El Forum - 08-04-2010
[eluser]patie[/eluser]
[quote author="WanWizard" date="1280969465"]If it's a one-to-many, that shouldn't be necessary, your FK should be ok like this.
You are doing something wrong somewhere, I just created a test database with your 4 tables, created the models, and ran the query I posted before.
Result:
Code:
SELECT `order_albums`.*, `orders`.`user_id` AS order_user_id, `orders`.`status` AS order_status, `order_users`.`id` AS order_user_id, `order_users`.`name` AS order_user_name
FROM (`order_albums`)
LEFT OUTER JOIN `orders` orders ON `orders`.`id` = `order_albums`.`order_id`
LEFT OUTER JOIN `users` order_users ON `order_users`.`id` = `orders`.`user_id`
GROUP BY `order_albums`.`order_id` ORDER BY `order_albums`.`id` DESCSo, I still think there's something wrong in the definition of your relationships.
Also, note the naming conventions. The model is called "User", the foreign key is called "user_id", the table is called "Users". Note the plural form![/quote]
1. i use $table definition so User model have $table = 'user'; ....
omg.. yes i have some result
Code:
SELECT `order_albums`.*, `orders`.`user_id` AS order_user_id, `orders`.`status` AS order_status, `order_users`.`id` AS order_user_id, `order_users`.`name` AS order_user_name
FROM (`order_albums`)
LEFT OUTER JOIN `orders` orders ON `orders`.`id` = `order_albums`.`order_id`
LEFT OUTER JOIN `users` order_users ON `order_users`.`id` = `orders`.`user_id`
GROUP BY `order_albums`.`order_id` ORDER BY `order_albums`.`id` DESCbecause i dont want this
Code:
LEFT OUTER JOIN `users` order_users ON `order_users`.`id` = `orders`.`user_id`understand me ?
)i want join USER with ORDER with foreign key . not with next new table - order_users
I do not understand or you ?
))
DMZ 1.7.1 (DataMapper OverZealous Edition) - El Forum - 08-05-2010
[eluser]WanWizard[/eluser]
Have you ever tested this code and checked the results, or is this a theoretical excercise?
Have you read up on SQL syntax?
In this query, 'order_users' is a table alias, to make sure that if you include the same table twice (on two foreign keys linking to the same table) you don't get errors. It is not a new table.
DMZ 1.7.1 (DataMapper OverZealous Edition) - El Forum - 08-05-2010
[eluser]patie[/eluser]
[quote author="WanWizard" date="1281008505"]Have you ever tested this code and checked the results, or is this a theoretical excercise?
Have you read up on SQL syntax?
In this query, 'order_users' is a table alias, to make sure that if you include the same table twice (on two foreign keys linking to the same table) you don't get errors. It is not a new table.[/quote]
YOU RIGHT. my bad. THANKS YOU VERY MUCH !