QM is not useful for queries like this?

[ToxicalNoob3062]

SELECT p.id, img, title, description, stock, price
FROM PRODUCTS p
JOIN (
    SELECT ID FROM SHOPS WHERE ID = :shopId -- Filter by shopId
) AS shopFiltered ON p.SHOP_ID = shopFiltered.ID
JOIN (
    SELECT PRODUCT_ID FROM PRODUCT_TAGS
    WHERE TAG_NAME IN (:tagList) -- Filter by tags
    GROUP BY PRODUCT_ID
    HAVING COUNT(DISTINCT TAG_NAME) = :tagListLen
) AS tagFiltered ON p.id = tagFiltered.PRODUCT_ID
JOIN (
    SELECT ID FROM PRODUCTS
    WHERE tsv @@ to_tsquery('english', :keywords)
) AS keywordFiltered ON p.ID = keywordFiltered.ID
ORDER BY p.id
LIMIT :limit
OFFSET  :offset;

The qm package docs is so poorly written without any examples that no developer can understand how to actually build a query using that without lots of trials!

I was trying to write this query using qm but I don't think qm is possible! I have no idea how the inner Joins function is working! Even there is also no text in docs that explains that!

So I endup with this (any help to convert it using qm is appreciatable) but now the problem is its not accepting the struct as params):

func (svc *InventoryServiceImpl) FindProducts(params *FindProductsParams, tx ...*sql.Tx) ([]*models.Product, error) {
	exec := svc.reqUtils.DecideExecutionContext(tx...)

	ctx, cancel := svc.reqUtils.GetContext(10)
	defer cancel()

	sql := `
	select p.id, img, title, description, stock, price
	from products p
    join (
    select id from shops where id = :shopId -- filter by shopId
	) as s on p.shop_id = s.id
 	`

	//check if tags are present
	if params.tagListLen > 0 {
		sql += `
	    join (
		select product_id from product_tags
		where tag_name in (:tagList) -- filter by tags
		group by product_id
		having count(distinct tag_name) = :tagListLen
		) as tf on p.id = tf.product_id
		`
	}

	if params.keywords != "" {
		sql += `join (
		select id from products
		where tsv @@ to_tsquery('english', :keywords)
		) as k on p.id = k.id
		`
	}

	sql += `
	order by p.id
	limit :limit
    offset :offset;
	`

	//execute the query
	rows, err := exec.QueryContext(ctx, sql, params)
	if err != nil {
		return nil, err
	}

	//parse the results
	products := []*models.Product{}
	for rows.Next() {
		product := &models.Product{}
		err = rows.Scan(&product.ID, &product.Img, &product.Title, &product.Description, &product.Stock, &product.Price)
		if err != nil {
			return nil, err
		}
		products = append(products, product)
	}
	return products, nil
}

[stephenafamo]

What driver are you using for these named args? It looks like SQLite to me.

The issue is not with SQLBoiler, but with how you're passing named args. This line:

rows, err := exec.QueryContext(ctx, sql, params)

Should be:

rows, err := exec.QueryContext(
    ctx, SQL, 
    sql.Named("shopId", params.ShopID), 
    sql.Named("tagList", params.TagList),
    // and so on....
)