Použitie ->where() a ->order v modeli

Andurit

Čaute,
snažím sa naučiť sa používať funkcie where() a order() do SQL dotazov, jak kvôli prehľadnosti tak zrejme aj kvôli akejsi jednoduchosti.

Evidetne robím niečo zle a preto budem rád ak mi to niekto pochopiť.
Ako príklad som si skúsil takýto dotaz:

SELECT c.id, c.firstname, c.surname, c.email, c.process, c.search_work, c.note,
 group_concat(DISTINCT ce.enforcement) as enfor,
 group_concat(DISTINCT cc.city) as city
FROM candidates AS c
LEFT JOIN candidates_enforcement as ce on c.id = ce.candidates_id
LEFT JOIN candidates_city as cc on c.id = cc.candidates_id
WHERE c.id='8'
GROUP BY c.id, c.firstname, c.surname, c.email;

Samozrejme to funguje, skúsil som rovnaký dotaz prepísať cez where() aorder()
Celá metóda vyzerá nejako takto:

	function detailById($id)
	{
		return $this->db->query('
			SELECT c.id, c.firstname, c.surname, c.email, c.process, c.search_work, c.note,
			 group_concat(DISTINCT ce.enforcement) as enfor,
			 group_concat(DISTINCT cc.city) as city
			FROM candidates AS c
			LEFT JOIN candidates_enforcement as ce on c.id = ce.candidates_id
			LEFT JOIN candidates_city as cc on c.id = cc.candidates_id
			')->where('c.id', $id)->order('c.id, c.firstname, c.surname, c.email');
	}

dostávam ale error: Call to undefined method Nette\Database\ResultSet::where()

Čo robím zle?

David Matějka

Nemuzes takhle kombinovat psani SQL a Database\Table vrstvu. Musis si vybrat jedno nebo druhy

Andurit

Ako by to malo vyzerať v SQL je prakticky v otázke, môžeš dať ukážku ako by to teda malo vyzerať plne v réžii Database\Table?

Editoval Andurit (2. 6. 2015 17:25)

David Matějka

query:

    return $this->db->query('
        SELECT c.id, c.firstname, c.surname, c.email, c.process, c.search_work, c.note,
         GROUP_CONCAT(DISTINCT ce.enforcement) AS enfor,
         GROUP_CONCAT(DISTINCT cc.city) AS city
        FROM candidates AS c
        LEFT JOIN candidates_enforcement AS ce on c.id = ce.candidates_id
        LEFT JOIN candidates_city AS cc on c.id = cc.candidates_id
        c.id = ? ORDER BY c.id, c.firstname, c.surname, c.email', $id);
//vsimni si ^^^^^ otazniku
// a pis keywordy a funkce velkyma pismenama, nette by to jinak mohlo spatne escapovat (naopak tabulky a sloupcecky pis malymi)

a v ndbt by to mohlo jit zhruba:

return $this->db->table('candidates')
	->select('candidates.*,
GROUP_CONCAT(:candidates_enforcement.enforcement) AS enfor,
GROUP_CONCAT(:candidates_city.city) AS city
')
->where('candidates.id', $id)->order('candidates.id, candidates.firstname, candidates.surname, candidates.email');