[postmodern-devel] conditional query building

[Jens Teich]

Marijn Haverbeke wrote:
> Hello Jens,
> 
> I'm not entirely sure what you want to do here. Do you want the IF to
> appear in the query, or the query to be adjusted depending on the
> outcome of the IF? In the second case, look into SQL-COMPILE, which
> allows the query s-expression to be built at run-time (rather than
> compile time, as with the SQL macro).
> 
> Cheers,
> Marijn

I fount two ways to solve my problem.

First is with a macro:

(defmacro element (&key guid guid-parent type-id type-not-equal lang)
  `(postmodern:query (:order-by
                      (:select 'guid 'name 'type-id 'name-css
                       :from   'projekt-elemente
                       :where  (:and
                                ,(if guid `(:= 'guid ,guid) t)
                                ,(if guid-parent
                                   `(:= 'struct-guid-parent-node ,guid-parent) t)
                                ,(if type-id `(:= 'type-id ,type-id) t)
                                ,(if type-not-equal `(:not (:= 'type-id ,type-not-equal)) t)
                                (:= 'lang-id (or ,lang *default-lang*))))
                      'struct-sort-order)))

Second is with let:

(defun werte (lang merkmal-id &key bereich gruppe reihe)
    (remove-duplicates ; statt DISTINCT
     (let ((sql (cond (reihe   (postmodern:sql (:= 'guid-reihe   reihe)))
                      (gruppe  (postmodern:sql (:= 'guid-gruppe  gruppe)))
                      (bereich (postmodern:sql (:= 'guid-bereich bereich)))
                      (t "TRUE"))))
       (postmodern:query (:order-by
                          (:select 'wert
                           :from   'projekt-merkmale-werte
                           :where (:and
                                   (:= 'lang-id    lang)
                                   (:= 'merkmal_id merkmal-id)
                                   (:raw sql)))
                          'wert)))
     :test #'equal))

Don't know if there is a more direct way to achieve this.

Thanks for your library. It rocks.

Jens