Symfony2 query array matching

1
votes

I'm trying to make a query with arrays in Symfony2 and I'm having some problems.

I have two entities with one array field (I use it as a Tag field). My intention is, when I have on object of Entity1, I would query the DB to select objects of Entity that have at least one common tag. Anyone knows how can I do it?

I would also like to know if it is possible to sort the results of as many matches to low (ie, those who have more tags in common, appearing first).

Example of the entities structure:

Entity1:
  - id = 1
  - tags = {'Football', 'Soccer', 'Premier Leage'}

Entity2:
  - id = 2
  - tags = {'Football', 'Hockey', 'Tennis'}

Many thanks guys!

UPDATE:

Now, I have a Entity1 and Entity2 with ManyToMany to a Entity called Tags. Now, this SQL gets me the correct Entity2 ID:

SELECT e2.entity2_id, COUNT( e2.entity2_id ) AS common_tag_count
FROM entity2_tags AS e2
INNER JOIN entity1_tags AS e1 ON e2.tags_id = e1.tags_id
WHERE e1.entity1_id =3
GROUP BY e2.entity2_id
HAVING e2.entity2_id !=2
ORDER BY COUNT( e2.tags_id ) DESC

Now, I can't combine the two tables in a simple JOIN with DQL, because in DQL we can't use the Doctrine automatic tables (entity1_tags and entity2_tags).

Anyone knows how can I traslate the SQL code to DQL?

2
arrayssymfonydoctrine-ormmatchingdql

2 Answers

1
votes

Doctrine ORM array type is saved in database as serialized data. Since it's just a string, you are not able to make such queries as you desire. You have two possible ways to solve it:

  1. Use separate table for your tags with ManyToMany or ManyToOne relationship
  2. Use doctrine simple_array type to save tags and then query them using Full-Text search. Full-Text is not available for all RDBMS, so you might need some search server, like sphinx or similar.
1
votes

Im using:

$qb = $filterQuery->getQueryBuilder();
$orX = $filterQuery->getExpr()->orX(); // or andX

foreach ($values['value'] as $value) {
    $column = $filterQuery->getExpr()->concat($filterQuery->getExpr()->literal(','), $filterQuery->getExpr()->concat($field, $filterQuery->getExpr()->literal(',')));
    $orX->add($filterQuery->getExpr()->like($column, $filterQuery->getExpr()->literal("%,{$value},%")));
}

$qb->andWhere($orX);