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

Popular posts from this blog

javascript - Count length of each class -

What design pattern is this code in Javascript? -

hadoop - Restrict secondarynamenode to be installed and run on any other node in the cluster -