Re: [symfony-users] [Doctrine2] Delete query + JOIN. (How to translate this SQL query into DQL)
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
Re: [symfony-users] [Doctrine2] Delete query + JOIN. (How to translate this SQL query into DQL)
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
Re: [symfony-users] [Doctrine2] Delete query + JOIN. (How to translate this SQL query into DQL)
Hi, read a manual with MEMBER OF function, I think in your case it should
fit
On Thu, Jun 16, 2011 at 6:04 AM, oscar balladares liebegr...@gmail.comwrote:
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
--
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
Re: [symfony-users] [Doctrine2] Delete query + JOIN. (How to translate this SQL query into DQL)
Thanks, I'll try to digg into it.
2011/6/16 Gediminas Morkevicius gediminas.morkevic...@gmail.com
Hi, read a manual with MEMBER OF function, I think in your case it should
fit
On Thu, Jun 16, 2011 at 6:04 AM, oscar balladares liebegr...@gmail.comwrote:
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
--
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
[symfony-users] [Doctrine2] Delete query + JOIN. (How to translate this SQL query into DQL)
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
