tiny ‘n’ smart
database layer

Odkazy: dibi | API reference

Oznámení

Omlouváme se, provoz fóra byl ukončen

Jak na and a orv jedné podmínce

Úvod » Dibi
Upozornění: Tohle vlákno je hodně staré.

před 5 lety

Tirus91
Generous Backer | 199

ahoj,
potřeboval bych poradit jak udělat %or a %and v jednom WHERE v DibiConnection v Nette

snažím se si zjednodušit něco takéhleho

if ($showAll) {
    if ($topicId == null) {
        $res = $this->database->query('SELECT article_post.*, article_topic.title as topic_title, security_users.id as user_id, security_users.login as author_login FROM article_post LEFT JOIN security_users ON article_post.security_users_id = security_users.id LEFT JOIN article_topic ON article_post.article_topic_id = article_topic.id');
    } else {
        $res = $this->database->query('SELECT article_post.*, article_topic.title as topic_title, security_users.id as user_id, security_users.login as author_login FROM article_post LEFT JOIN security_users ON article_post.security_users_id = security_users.id LEFT JOIN article_topic ON article_post.article_topic_id = article_topic.id WHERE article_topic_id = %i', $topicId);
    }
} else {
    if ($topicId == null) {
        $res = $this->database->query('SELECT article_post.*, article_topic.title as topic_title, security_users.id as user_id, security_users.login as author_login FROM article_post LEFT JOIN security_users ON article_post.security_users_id = security_users.id LEFT JOIN article_topic ON article_post.article_topic_id = article_topic.id  WHERE ( %or ', array(array('published_from <=', $dateTime), array('published_from is null')), ') AND ( %or ', array(array('published_to >=', $dateTime), array('published_to is null')), ')');
    } else {
        $res = $this->database->query('SELECT article_post.*, article_topic.title as topic_title, security_users.id as user_id, security_users.login as author_login FROM article_post LEFT JOIN security_users ON article_post.security_users_id = security_users.id LEFT JOIN article_topic ON article_post.article_topic_id = article_topic.id WHERE article_topic_id = %i', $topicId, ' AND ( %or ', array(array('published_from <=', $dateTime), array('published_from is null')), ') AND ( %or ', array(array('published_to >=', $dateTime), array('published_to is null')), ')');
    }
}

před 5 lety

Milo
Nette Core | 1119

Máš víc možností. Buď si poskládat argumenty pro query do pole:

$sql = array();
$sql[] = '
    SELECT
        article_post.*,
        article_topic.title as topic_title,
        security_users.id as user_id,
        security_users.login as author_login
    FROM
        article_post
        LEFT JOIN security_users ON article_post.security_users_id = security_users.id
        LEFT JOIN article_topic ON article_post.article_topic_id = article_topic.id
    WHERE
        1=1
';

if ($topicId !== NULL) {
    $sql[] = '
        AND article_topic_id = %i
    ';
    $sql[] = $topicId;
}

if (!$showAll) {
    $sql[] = '
        AND (
            published_from IS NULL
            OR published_from <= %t
            OR published_from >= %t
        )
    ';
    $sql[] = $dateTime;
    $sql[] = $dateTime;
}

$this->database->query($sql);

Nebo použít modifikátory %if a %end:

$this->database->query('
    SELECT
        article_post.*,
        article_topic.title as topic_title,
        security_users.id as user_id,
        security_users.login as author_login
    FROM
        article_post
        LEFT JOIN security_users ON article_post.security_users_id = security_users.id
        LEFT JOIN article_topic ON article_post.article_topic_id = article_topic.id
    WHERE
        1=1
        %if', $topicId !== NULL, '
            AND article_topic_id = %i', $topicId, '
        %end
        %if', !$showAll, '
            AND (
                published_from IS NULL
                OR published_from <= %t', $dateTime, '
                OR published_from >= %t', $dateTime, '
            )
        %end
');

a nebo by to asi šlo zapsat jedním velkým strukturovaným polem, ale do toho bych se moc nepouštěl.

před 5 lety

Tirus91
Generous Backer | 199

Super, ruku ti líbám :) Toto bylo asi zatím to nejhorší co sem nemohl rozjet na DIBI :) ještě jak je to s LIKE? Nějak sem nemohl najít patřičnou dokumentaci s poměrně dobrým samplem

před 5 lety

Milo
Nette Core | 1119

No, normálně, LIKE funguje ;)

před 5 lety

Tirus91
Generous Backer | 199

jj, už vím :) dal jsem tam vždy jen modifikátor a děsně jsem se vztekal že to nefunguje (pak mi došlo že tam musím dát ještě ‚LIKE‘ :) )

Ještě jednou mockrát děkuji :)