| Autor | Zpráva | ||
|---|---|---|---|
| jefitto44 Profil |
#1 · Zasláno: 22. 7. 2015, 14:09:27
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 |
#2 · Zasláno: 22. 7. 2015, 14:17:54
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 |
#3 · Zasláno: 22. 7. 2015, 14:27:54 · Upravil/a: Moderátor (editace znemožněna) 22. 7. 2015, 23:09:58
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). |
||
|
Časová prodleva: 9 let
|
|||
0