Skip to content

Instantly share code, notes, and snippets.

@broklyngagah
Created December 20, 2015 00:40
Show Gist options
  • Save broklyngagah/90d5fe48c4a46afa3cd4 to your computer and use it in GitHub Desktop.
Save broklyngagah/90d5fe48c4a46afa3cd4 to your computer and use it in GitHub Desktop.
Phalcon Query Builder Expression

I have a query like this (native sql)

SELECT
	DISTINCT(P.product_variant_code) AS product_variant_code,
	P.product_name AS product_name,
	P.variant_name AS variant_name,
	P.is_host_to_host AS is_host_to_host,
	P.has_child AS has_child,
	P.variant_options AS variant_options,
	case when upp.user_sell_price is null then p.sell_price else upp.user_sell_price end as user_price /* this is the problem */
FROM
	products AS P
LEFT JOIN users_product_price AS upp ON P.product_id = upp.product_id 
AND upp.member_code = '...'
ORDER BY P.product_name ASC

But look like code above does not work when using phalcon query builder.

// phalcon query builder
->columns([
      'p.product_name', 'p.product_variant_code', 'p.variant_name',
      'p.is_host_to_host', 'p.has_child', 'p.variant_options',
      "expr(CASE WHEN upp.user_sell_price IS NULL THEN p.sell_price ELSE upp.user_sell_price END) as user_price"
  ])
  ->addFrom($this->model, 'p')
  ->leftJoin(
      UsersProductPrice::class,
      'p.product_id = upp.product_id and upp.member_code = :member_code:',
      'upp'
  );
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment