Autor Zpráva
jefitto44
Profil
Mám tabuľku hráčov - players.
Mám druhú tabuľku players_extra.

V tabuľke players sú základné veci - player_id, firstname, lastname, shirtnumber atď atď.
V tabuľke players_extra sú ku každému hráčovi doplňujúci informácie. Konkrétne tieto stlpce:
- player_extra_id
- player_id (to určuje ktorého hráča sa to týka)
- player_extra_type (určuje typ hodnoty)
- player_extra_value (určuje samotnú hodnotu)

napr. player David Beckham (player_id=12) má v players_extra
1|12|University|NULL (toto znamená, že nemá vyštudovanú universitu)
2|12|experience|9 (toto znamená, že má 9 rokov skúsenosti v profesionalnom futbale)
3|12|birth_place|Edinburg (toto znamená, že sa narodil v edinburgu)
atď...

Ja potrebujem vytvoriť query, ktoré by vybralo všetko (*) z tabuľky players, pripojilo sa do players_extra, ale vybralo iba ten riadok, kde player_extra_type je University a ostatné riadky ignorovalo. Ako sa dá toto dosiahnuť? Ak to skúšam klasickým joinom, tak mi vyhodí tri krát to isté (na každý players_extra jeden výsledok, dokopy tri výsledky).

Ďakujem za pomoc, hádam ste to z toho popisu dokázali pochopiť :)
Camo
Profil
jefitto44:
Skúsil si join on p.id = pe.player_id and pe.player_extra_type = University ?
Ak to nebude fungovať tak daj aj svoje sql.
jefitto44
Profil
Ono je to v podstate zložitejšie, ja som sa to snažil zjednodušiť... query je to jak fras.

select `p`.`uuid` as `personId`, `p`.`matchName` as `name`, `p`.`type` as `type`, `p`.`position` as `position`, `p`.`nationality` as `nationalityId`, `a1`.`nationality` as `nationality`, `p`.`firstname` as `firstName`, `p`.`middlename` as `middleName`, `p`.`lastname` as `lastName`, `p`.`date_of_birth` as `dateOfBirth`, `p`.`place_of_birth` as `placeOfBirth`, `p`.`country_of_birth` as `countryOfBirthId`, `a2`.`name` as `countryOfBirth`, `p`.`height` as `height`, `p`.`weight` as `weight`, `p`.`university` as `university`, `p`.`status` as `status`, `pe`.`value` as `yearWentPro` from `team` as `t` left join `team_people` as `tp` on `t`.`team_id` = `tp`.`team_id` left join `people` as `p` on `p`.`people_id` = `tp`.`people_id` left join `area` as `a1` on `a1`.`area_id` = `p`.`nationality` left join `area` as `a2` on `a2`.`area_id` = `p`.`country_of_birth` left join `people_extra` as `pe` on `pe`.`people_id` = `p`.`people_id` where `t`.`team_id` = 195 and `pe`.`people_extra_type_id` = year_went_pro

Query je z Laravel buildera:

$result =   DB::connection($this->connection)->table("team as t")
                    ->leftJoin('team_people as tp','t.team_id','=','tp.team_id')
                    ->leftJoin("people as p","p.people_id","=","tp.people_id")
                    ->leftJoin("area as a1","a1.area_id","=","p.nationality")
                    ->leftJoin("area as a2","a2.area_id","=","p.country_of_birth")
                    ->leftJoin("people_extra as pe","pe.people_id","=","p.people_id")
                    /*->leftJoin("people_extra as pe2","pe2.people_id","=","p.people_id")
                    ->leftJoin("people_extra as pe3","pe3.people_id","=","p.people_id")*/
                    ->select(
                        ".uuid as personId", "p.matchName as name", "p.type as type", "p.position as position", 
                        "p.nationality as nationalityId", "a1.nationality as nationality", "p.firstname as firstName", "p.middlename as middleName",
                        "p.lastname as lastName", "p.date_of_birth as dateOfBirth", "p.place_of_birth as placeOfBirth",
                        "p.country_of_birth as countryOfBirthId","a2.name as countryOfBirth", "p.height as height", 
                        "p.weight as weight", "p.university as university",
                        "p.status as status", "pe.value as yearWentPro"//, "pe2.value as draftPlace","pe3.value as draftRound"
                    )
                    ->where("t.team_id","=",$teamId)
                    ->where("pe.people_extra_type_id", "=","year_went_pro")
                    /*->where("pe2.people_extra_type_id","=","draft_place")
                    ->where("pe3.people_extra_type_id","=","draft_round")*/
                    ->get();

Toto čo je zakomentovane som skušal, ale nevyšlo... nefunguje to tak, nevratilo mi nič z DB
Camo
Profil
jefitto44:
Where ti nepomôže a neviem čo ten query bilder dokáže. JOIN ON AND je niečo iné ako JOIN ON WHERE. Bolo by fajn keby to Laravel zvládol ale ja neviem ešte som sa v kurze tak ďaleko nedostal :)

EDIT:
Našiel som toto ale či to funguje neviem:
http://stackoverflow.com/questions/17404743/laravel-4-query-builder-left-join-and-query
Tu je ten bilder http://stackoverflow.com/questions/17404743/laravel-4-query-builder-left-join-and-query (asi).

Vaše odpověď

Mohlo by se hodit


Prosím používejte diakritiku a interpunkci.

Ochrana proti spamu. Napište prosím číslo dvě-sta čtyřicet-sedm: