createCommand sql query not working in plugin

Question

Hi I have a plugin which I am running an sql statement from.

$users = craft()->db->createCommand("SELECT craft_users.id as userId, craft_users.firstName, craft_users.lastName, craft_users.email FROM craft_users INNER JOIN craft_content on craft_users.id = craft_content.elementId WHERE craft_content.field_interests LIKE '%\"8948\"%'");

The query is not returning the expected results from the createCommand function in fact it returns an empty array. But if I run it directly in the database it works perfectly and returns the entries.

I also tried building this with the query builder but it was throwing errors and not finding the user table.

Answer 1

Thanks to @carlcs for pointers managed to get this working with the following:

$criteria = craft()->elements->getCriteria(ElementType::User);
$criteria->limit = NULL;

$query = craft()->elements->buildElementsQuery($criteria);
$params = array(
    ':interest' => '%"8948"%',
);
$query->andWhere("field_interests LIKE :interest", $params);
$result = $query->queryAll() ;