Hi again,

I'm having a lot of trouble selecting a specific row to display in my report.

This is my SQL:

--ACCESS=access content
SELECT COUNT(*) as  total FROM users
UNION SELECT COUNT(u.uid) as approved FROM users u
INNER JOIN field_data_field_approved ca ON u.uid = ca.entity_id
WHERE ca.field_approved_value = 1

I want to display it like so:

Total users: {total} / Approved users: {approved}

I can't seem to find the right xPath to use here:

New users: <span id="total_users" frx:block="community/total_users" frx:foreach="???"> {total}</span> / New Approved users: <span id="total_users" frx:block="community/total_users" frx:foreach="???"> {total}</span>

I tried using {=total} and a lot of other stuff, but I can't get it to work.

I could split it in two SQLs, but i'd rather just have one for this.

Thanks!

Comments

metzlerd’s picture

When you do a union you are placing both values int he same column, which is not what you want.

I would do something like:

SELECT
  count(*) as total,
  count(CASE WHEN field_approved_value = 1 THEN 1 END ) as approved
  FROM users LEFT JOIN 
    field_data_field_approved ca ON ca.entity_id = u.uid

This will put them into separate columns and works across databases.

Alternatively you can use group by expressions to roll up the values of the field like so:

SELECT field_approved_value, COUNT(1) as total
  FROM users JOIN 
    field_data_field_approved ca ON ca.entity_id = u.uid
  GROUP BY field_approved_value WITH ROLLUP

Assuming mysql. You can still get inividual values with xpath expressions from this query by using a syntax something like {row[field_approved_value=1]/total}, but it gets tricky trying to get the null one for the rollup.

I'd experiment with these two forms and see which you like best. The first one is my "goto" for crosstab style presentations, the second is better for providing summary totals with aggregates.

Growiel’s picture

Status: Active » Fixed

OK thanks you.

I though that since I returned two results I could select one with xPath, even if they were on the same column.

Now that I know this limitation, I'll use the first type of query.

Thanks a lot !

metzlerd’s picture

For the record. You can select one with xpath, I just think the other query makes more sense. If you don't use for each attruute you can use xpath syntaxes similar to {row[1]/total} to get the nth row of an element.

metzlerd’s picture

Assigned: Unassigned » metzlerd

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.