Hi David.
I tried your query, but it doesn't do the trick, but thanks anyway.
I've been digging and found that Delete doesn't allow JOIN.
Thanks again, for your help.
2011/6/16 David Mann <ni...@codingninja.com.au>
> **
> 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 = 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 = 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 IN (SELECT ph.id FROM
> VendorBundle:Photo ph JOIN ph.bedrooms bp JOIN bp.bedroom b WHERE 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 IN (SELECT bp.photo_id FROM
> VendorBundle:BedroomPhoto bp JOIN bp.bedroom b WHERE 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
>
--
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
