[manuel-84]

I tried adding a calculated column into the "select" property of a CDbCriteria, it doesn't work . I also tried using an array.

	$q = new CDbCriteria(array(
  	'scopes' => 'notOwnedByUser',
  	'select' => '*, (MATCH (tbl_book_ftsearch.title, tbl_book_ftsearch.description) AGAINST ('*$match*')) AS SCORE',
  	'join' => 'LEFT JOIN tbl_book_ftsearch ON t.id = tbl_book_ftsearch.id',
  	'condition' => "LOWER(author) LIKE :match OR LOWER(publisher) LIKE :match OR MATCH (tbl_book_ftsearch.title, tbl_book_ftsearch.description) AGAINST ('*$match*' IN BOOLEAN MODE)",
  	'order' => 'score DESC',
  	'params' => array(':match' => "%$match%")
	));

Quote

[system.db.CDbCommand] CDbCommand::fetchAll() failed: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'score' in 'order clause'. The SQL statement executed was: SELECT 0 FROM `tbl_book` `t` LEFT JOIN tbl_book_ftsearch ON t.id = tbl_book_ftsearch.id WHERE (t.id NOT IN (SELECT book_id FROM tbl_user_book WHERE tbl_user_book.user_id = :user_id)) AND (LOWER(author) LIKE :match OR LOWER(publisher) LIKE :match OR MATCH (tbl_book_ftsearch.title, tbl_book_ftsearch.description) AGAINST ('*fdgsfdg*' IN BOOLEAN MODE)) ORDER BY score DESC. Bound with :user_id='2', :match='%fdgsfdg%'.

I'm doing something wrong or is it a bug?
Thanks

[manuel-84]

I also tried

 
  	'select' => array('*', '(MATCH (tbl_book_ftsearch.title, tbl_book_ftsearch.description) AGAINST ('*$match*')) AS SCORE'),

I've found this workaround that seems to work without using select

'order' => '(MATCH (tbl_book_ftsearch.title, tbl_book_ftsearch.description) AGAINST ('*$match*')) DESC',

[CeBe]

Sure it isn't a case issue? SCORE vs score?

[manuel-84]

CeBe, on 27 April 2012 - 06:14 PM, said:

Sure it isn't a case issue? SCORE vs score?

Loll, no , I tried also with all lowercase

The problem is that the generated query is

Quote

SELECT 0 FROM

[CeBe]

Can you give the code you use to run the query?

[manuel-84]

nope, just figured out now that I was writing

'select' => '*, (MATCH (tbl_book_ftsearch.title, tbl_book_ftsearch.description) AGAINST ('*$match*')) AS score',

instead of

'select' => "*, (MATCH (tbl_book_ftsearch.title, tbl_book_ftsearch.description) AGAINST ('*$match*')) AS score",

now the select is built as it should be but I have to modify the query a little to make it works because now I get "Can't find FULLTEXT index matching the column list."

better go bed now and resume tomorrow