SQL query to get count of property values by value resource ids

Omeka S Modules
1

Hello everyone,

I am currently developing a module that extends the Datavis module. I would like to get the count of property values for resource values. The CountPropertyValues visualization does this but only for literal values.

I would like to be able to make this request in my module:

SELECT *
FROM value AS v
WHERE v.property_id = 235
AND v.value_resource_id = 1701;

and then count the number of items where property 235 is used to point to item 1701.

I have tried many times, but I cannot find a way to add the search parameter “value_resource_id” correctly.

Here is the content of my function getDataset() :

$em = $services->get('Omeka\EntityManager');
$qb = $em->createQueryBuilder();
$qb->from('Omeka\Entity\Value', 'v');
$qb->select('v.value, v.value_resource_id');
$qb->andWhere('v.property = 139');
$qb->andWhere('v.value_resource_id = 1701');
$dataset = $qb->getQuery()->getScalarResult();
return $dataset;

for which I get this error:

Error: Class Omeka\Entity\Value has no field or association named value_resource_id in [...]/vendor/doctrine/orm/src/Query/QueryException.php:45

Can you tell me how to make this request?

Thanks!
Vincent

2

Try v.valueResource instead of v.value_resource_id.

3

Thank you @jimsafley for your reply. I tested:

$em = $services->get('Omeka\EntityManager');
$qb = $em->createQueryBuilder();
$qb->from('Omeka\Entity\Value', 'v');
$qb->select('v.value, v.valueResource');
$qb->andWhere('v.property = 139');
$qb->andWhere('v.valueResource = 1701');
$dataset = $qb->getQuery()->getScalarResult();
return $dataset;

and i get this error:

Next Doctrine\ORM\Query\QueryException: [Semantical Error] line 0, col 18 near 'valueResource': Error: Invalid PathExpression. Must be a StateFieldPathExpression. in [...]vendor/doctrine/orm/src/Query/QueryException.php:45

without v.valueResource in the select instruction, I have an empty result.

I will continue my tests with v.valueResource

4

I finally managed to make the query; I had to add a JOIN:

$qb = $em->createQueryBuilder();
$expr = $qb->expr();
$qb->from('Omeka\Entity\Value', 'v');
$qb->select('COALESCE(v.value, valueResource.id) AS val, COUNT(valueResource) AS count');
$qb->leftJoin(\Omeka\Entity\Item::class, 'valueResource', Join::WITH, $expr->eq('v.valueResource', 'valueResource'));
$qb->andWhere('v.property = 235');
$qb->groupBy('val');
$qb->orderBy('count', 'desc');
$dataset = $qb->getQuery()->getScalarResult();