Hey mate,
Give this a shot, don't have ability to test right now though sorry.
delete VendorBundle:Photo p, bp LEFT JOIN p.bedrooms bp LEFT JOIN
bp.bedroom b where b.id <http://b.id> = 13
The following queries are valid though
|if user chose to delete related rows as well, then:
DELETE
p, # The photo
bp, # The relation
b # The bedroom
FROM
photo p
INNER JOIN
bedroom_photo bp||# The relation join|
|ON
bp.id_photo = p.id||# On the photos id|
|INNER JOIN # The bedroom join
bedroom b ON b.id = bp.id_bedroom # The bedrooms id
WHERE
b.id = 1;
otherwise:
DELETE
p, bp
FROM
photo p
INNER JOIN
bedroom_photo bp ON bp.id_photo = p.id
INNER JOIN
bedroom b ON b.id = bp.id_bedroom
WHERE
b.id = 1;
||
|Hope this helps!|
|
Regards,/
David Mann - Coding Ninja/
On 6/16/2011 2:04 PM, oscar balladares wrote:
Hi everybody!
This might go in the Doctrine's mailing list, but knowing you all S2
people are working with Doctrine 2, I'm feeling
lucky that you could help me out.
This is the kind of stuff I want to achieve:
DQL: delete VendorBundle:Photo p JOIN p.bedrooms bp JOIN bp.bedroom b
where b.id <http://b.id> = 13
What debugger say:
Error: Expected end of string, got 'JOIN'
A little background:
I have 3 entities, two are main entities, and the last one is a bridge
between the firsts.
1) Bedroom
2) Photo
3)Bedroom_photo
bedroom_photo is some sort of the many to many relationship between 1
and 2. But 1 and 2 are not associated directly
like a real many to many because 3 has an extra property. The
association may be described like a oneToMany-ManyToOne.
A bedroom can have many photos and a photo can be owned by many
bedrooms (the client could use a photo to describe many bedrooms).
This association allow a photo to be uploaded without being explicitly
related to any bedroom.
3rd entity's mapping is:
fields:
is_public:
type: boolean
manyToOne:
bedroom:
targetEntity: Vendor\VendorBundle\Entity\Bedroom
inversedBy: photos
photo:
targetEntity: Vendor\VendorBundle\Entity\Photo
inversedBy: bedrooms
The associations are bidirectional.
So What I want is if a client tries to delete a bedrooms, prompt him
also to choose to delete only the photo association info (rows on
bedroom_entity) or
to delete actually the associated photos (also delete associated rows
on photo). I'm stucked trying to find a solution for last problem.
I also tried:
DQL: DELETE VendorBundle:Photo p WHERE p.id <http://p.id> IN (SELECT
ph.id <http://ph.id> FROM VendorBundle:Photo ph JOIN ph.bedrooms
bp JOIN bp.bedroom b WHERE b.id <http://b.id> = :id)
Debugger says:
General error: 1093 You can't specify target table 'foto' for update
in FROM clause
That is a known mysql issue. I digged a little bit and found you can't
update/delete from a table which clause is making reference to it.
I also tried:
DQL: DELETE VendorBundle:Photo p WHERE p.id <http://p.id> IN (SELECT
bp.photo_id FROM VendorBundle:BedroomPhoto bp JOIN bp.bedroom b WHERE
b.id <http://b.id> = :id)
Error: Class Vendor\VendorBundle\Entity\BedroomPhoto has no field or
association named photo_id
This is because the relation between Photo and BedroomPhoto is through
a mapping: photos, and doctrines doesn't see it as a property (the
photo_id column).
How the heck can I delete a bedroom's photos in one query?
Doing
foreach(bedroom->getBedroomPhotos() as $bp) {
$em->remove($bp->getPhoto()); } $em->flush() ;
That fires 2 queries for each photo that a bedroom has, the first one
is to remove the photo, and the 2nd to remove the relation on
bedroom_photo (I have setted
a cascade deletion on bedroom-bedroomPhoto, but running the code above
doesn't triggers it).
Anyway, I would want to do it in one query.
Any help, advice or constructive criticism would be greatfully welcome :D
Regards.
--
If you want to report a vulnerability issue on symfony, please send it
to security at symfony-project.com
You received this message because you are subscribed to the Google
Groups "symfony users" group.
To post to this group, send email to symfony-users@googlegroups.com
To unsubscribe from this group, send email to
symfony-users+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/symfony-users?hl=en
--
Regards, David Mann - /Coding Ninja/
--
If you want to report a vulnerability issue on symfony, please send it to
security at symfony-project.com
You received this message because you are subscribed to the Google
Groups "symfony users" group.
To post to this group, send email to symfony-users@googlegroups.com
To unsubscribe from this group, send email to
symfony-users+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/symfony-users?hl=en
