1

I have three content types :
Content type A (= leg)
Content type B (= transport)
Content type C (= ticket)

B has a node reference to A.
C has a node reference to B.

I have a views list with the following and aggregation setting is on;

FIELDS
Content: Nid
Content: From
Content: To
(field_transport) MIN(Content: Price) (for)
(term from field_transport_type) Taxonomy term: Name (by)

FILTER CRITERIA
Content: Published (Yes)
Content: Type (= Leg)
Content: From (exposed)
Content: To (exposed)

SORT CRITERIA
Content: Post date (desc)

RELATIONSHIP
(field_transport) Content: Leg (field_leg) - reverse
(field_leg) Content: Transport (field_transport) - reverse
(field_leg) Content: Transport Type
(term from field_transport_type) Taxonomy term: Content using Transport Type

The result with 3 records is shown as below :

RESULT
How to go from Bangkok to Singapore:
for $150.00
by Plane

How to go from Bangkok to Singapore:
for $200.00
by Train

How to go from Bangkok to Singapore:
for $80.00
by Bus

I want the result with the only last line ( for $80.00 by Bus ). The result is correct before I have no relationship with term from field_transport_type.
How can I do that with aggregation setting MIN which I already applied in the field list?
I'm using drupal 7.10.

[Edit]
Here is the screen cast of my view :

enter image description here Another thing is that the content type B (=transport) has a taxonomy term field (=transport type) which needs to be displayed in the field list. You can see them in the result - by Bus, by Plane, etc.

To be more clear, I attached another screen cast of my results : enter image description here
The result should be 2 records above. The last three lines is the group of a leg ( Bangkok to Singpore ). This cannot be done by changing the pager settings. Pager is 10 for the page, meaning - latest 10 legs with cheapest tickets.

[Edit]
When I changed field_transport_type in FIELDS into COUNT(DISTINCT Taxonomy term: Name), I got the result with the first two lines, but I lost the transport type names.

How to go from New York to London: for $120.00 by 1
How to go from Bangkok to Singapore: for $80.00 by 3

0

Finally, when I changed the last field in field list with aggregation MIN, I achieved the result.

(term from field_transfport_type) MIN(Taxonomy term: Name) (by)

0

My previous answer using MIN aggregation did not give me a correct result.
So, I had to write a query with subquery by myself. This query took me a few minutes to write and it gave me a exact result what I want.
I tried to create this with views for a week, but failed. What I can done with views is inner query only. May be I was missing something.

$result = db_query("SELECT leg.*, ( SELECT COUNT(*) from field_data_field_leg WHERE field_leg_nid = leg.nid AND bundle = :bundle ) AS reviews  FROM (
                SELECT n.nid, n.created, title, 
                    field_from_value origin, 
                    field_to_value destination, 
                    field_price_value price, 
                    term.name transport
                FROM node n 
                JOIN field_data_field_leg l ON n.nid = l.field_leg_nid
                JOIN field_data_field_from ff ON n.nid = ff.entity_id
                JOIN field_data_field_to ft ON n.nid = ft.entity_id
                JOIN field_data_field_transport t ON l.entity_id = t.field_transport_nid
                JOIN field_data_field_price p ON t.entity_id = p.entity_id
                JOIN field_data_field_transport_type ttype ON t.field_transport_nid = ttype.entity_id
                JOIN taxonomy_term_data term ON ttype.field_transport_type_tid = term.tid
                WHERE n.type = :type
                ORDER BY n.created DESC, field_price_value ASC
            ) AS leg GROUP BY nid 
            ORDER BY created DESC LIMIT 10", array(':bundle'=>'review', ':type' => 'leg'));

By doing like this, I had to create a page with a template from a sketch with all features which drupal views provides, such as exposed filter, etc.
I think it is not a good idea, but I had to do as I have no time enough.
If you have any better idea, you are welcome.

0

Perhaps you want to remove the post date sort criteria, and add an ascending sort criteria for content: price. If you only want one result value, change that in the Pager area.

  • The result shows three tickets of 3 transport types of a leg. This is just an example for a leg. There will be many legs in the result. The target is to group transport tickets of each leg ( three records in the result ), but min-price transport ticket of each leg must be at the top. Thus, I cannot change the pager setting. – Sithu Jan 21 '12 at 6:25
  • From MySQL perspective, it can be done with sub-query, but I don't know how to do with durpal views. – Sithu Jan 21 '12 at 6:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.