how to make an advanced query  #1062

Activity

1. aitva commented on Jun 28, 2021

   

   aitva

   on Jun 28, 2021

   Last edited by aitva

   Contributor

   More actions

   Hi @whistlesun,

   You can build advanced queries by using boolean operator in your WHERE clause:

   -- name: ListAuthors :many
   SELECT *
   FROM authors
   WHERE (@name::text = '' OR name = @name)
       AND (@birth::date = '0001-01-01' OR birth = @birth)
       AND (@bio::text = '' OR bio ILIKE '%' || @bio || '%');

   The conditions are evaluated as follow:

   • (@name::text = '' OR name = @name) returns true if @name is an empty string or evaluates to name = @name
   • AND (@birth::date = '0001-01-01' OR birth = @birth) is evaluated regardless of the result of the first condition, it returns true if @birth is the zero value of time.Time or evalues to birth = @birth
   • AND (@bio::text = '' OR bio ILIKE '%' || @bio || '%') is evaluated regardless of the result of the second condition, it returns true if @birth if an empty string or evaluates to bio ILIKE '%' || @bio || '%'

   You can add as many conditions as you want by following this pattern and you can try it in the playground.

   👍React with 👍10Reacted by Jeffrey Lo, Stas Karkavin, Steve Dignam, Alexandre Rodrigues, James Qualls, Juan Esteban Londoño Tabares, Luis Guzmán, Yann Coleu, ANiWarlock and Haseeb Majid

   👎React with 👎2Reacted by Yami Odymel and aimuz
2. mrsufgi commented on Jun 29, 2021

   

   mrsufgi

   on Jun 29, 2021

   Last edited by mrsufgi

   More actions

   This only works for non-nullable parameters, or for values where the zero-value is enough. how about the case for boolean?

   SELECT * FROM users WHERE (@active::boolean IS NULL OR active = @active)
   

   (optional filtering by active parameter)

   you can't use the zero-value of boolean (false).

   ofc this can also be true for strings. where string can be NULL, empty string or values string.
   Even for queries with LIMIT = $1
   you can workaround it using some super high number, but that is just a hack.
   you should be able to pass NULL so it falls back to LIMIT ALL.
3. aitva commented on Jun 29, 2021

   

   aitva

   on Jun 29, 2021

   Contributor

   More actions

   Yes, it is problematic if you need to use the zero value, there is a discussion on how to fix this #451. Meanwhile, you can use a second boolean to indicate the status of your filter:

   -- name: ListUsers :many
   SELECT * FROM users
   WHERE (NOT @is_active::boolean OR active = @active)
       (NOT @is_name::boolean OR name = @name);

   Calling the generated code from Go:

   type ListUsersFilter struct {
   	Name *string
   	Active *bool
   }
   
   func ListUsers(f ListUsersFilter) ([]*User, error) {
   	var active bool
   	if f.Active != nil {
   		active = *f.Active
   	}
   	var name string
   	if f.Name != nil {
   		name = *f.Name
   	}
   	users, err := db.ListUsers(context.TODO(), db.ListUsersParams{
   		IsActive: f.Active != nil,
   		Active: active,
   		IsName: f.Name != nil,
   		Name: name,
   	})
   	// ...
   }
4. aitva commented on Jun 29, 2021

   

   aitva

   on Jun 29, 2021

   Contributor

   More actions

   For queries with a LIMIT clause you can use NULLIF:

   SELECT *
   FROM users
   LIMIT NULLIF(@query_limit::int, 0);

   👍React with 👍1Reacted by mrsufgi
5. mrsufgi commented on Jun 29, 2021

   

   mrsufgi

   on Jun 29, 2021

   More actions

   @aitva seems like a viable workaround, I'm pretty sure there is an issue with NULLIF flawlessly but looks OK as a bandaid.
6. whistlesun commented on Jul 5, 2021

   

   whistlesun

   on Jul 5, 2021

   Author

   More actions

   tks
7. whistlesun

   closed this as completedon Jul 5, 2021
8. flymedllva commented on Nov 28, 2021

   

   flymedllva

   on Nov 28, 2021

   Contributor

   More actions

   @aitva And how to live with MySQL?
9. jalen-qian commented on Jan 21, 2022

   

   jalen-qian

   on Jan 21, 2022

   More actions

   There is the same problem on mysql.