sql - Select User data from the combination of four tables -
i have multiple tables such bit complex matter.
1)normal user table
user id|username 0 |tom 1 |dad 2 |bob
2)two tables area
smallarea id|name |bigarea 0 |blocka | 0(prefa) 1 |blockb | 0(prefa) 2 |blockc | 1(prefb) 3 |blockd | 1(prefb) 4 |blocke | 2(prefc)
3)two tables area
bigarea id | name 0 | prefa 1 | prefb 2 | prefc
it means
blocka , blockb belong prefa
blockc , blockd belong prefb
blocke belongs prefc
4)user , small area connection table
areaconnection user_id|smallarea 0(tom) | 0(blocka) 0(tom) | 1(blockb) 1(dad) | 2(blockc) 1(dad) | 3(blockd) 2(bob) | 3(blockd)
it means
tom belong blocka , blockb
dad belongs blockc , blockd
bob belongs blockd
then assume, have bigarea key 0(it means prefb)
i want select users belongs blocks belongs bigarea named prefb.
(prefb has blockc , blockd,
then dad , bob belongs blockc or blockd,
so dad , bob should selected)
how write pattern in sql? doctrine2 better such
select u.id,u.username user u inner join ??????
these entities.
class user.php { /** * @var integer * * @orm\column(name="id", type="integer",unique=true) * @orm\id */ private $id; /** * @var string * * @orm\column(type="string") */ private $username; /** * @orm\manytomany(targetentity="acme\userbundle\entity\smallarea") * @orm\jointable( * joincolumns={@orm\joincolumn(name="areaid", referencedcolumnname="id")}, * inversejoincolumns={@orm\joincolumn(name="id", referencedcolumnname="id")} * ) */ protected $smallarea; } class bigarea { /** * @var integer * * @orm\column(name="id", type="integer",unique=true) * @orm\id */ private $id; /** * @var string * * @orm\column(type="string") */ private $label; /** * * @orm\onetoone(targetentity="acme\userbundle\entity\smallarea", mappedby="prefecture") */ private $prefecture; } class smallarea{ /** * @var integer * * @orm\column(name="id", type="integer") * @orm\id * @orm\generatedvalue(strategy="auto") */ private $id; /** * @var string * * @orm\column(type="string") */ private $label; /** * * @orm\manytoone(targetentity="acme\userbundle\entity\bigarea", inversedby="prefecture*removethis : name of variable in bigarea.php*") * @orm\joincolumn(referencedcolumnname="id",nullable=false) */ private $prefecture; }
select * user inner join areaconnection on areaconnection.userid = user.id inner join smallarea on smallarea on smallarea.id = areaconnection.smallarea inner join bigarea on bigarea.id = smallarea.bigarea
doctrine dql. kindly take note never tried this, seems similiar hibernate. more, refer doctrine orm documentation
select u.username, sa.label user u join smallarea sa
Comments
Post a Comment