AnsweredAssumed Answered

convert SQL query to $bean->? code

Question asked by community-support on Jan 13, 2014
Latest reply on Jan 13, 2014 by community-support
I am trying, unsuccessfully, to stop using SQL queries and start using only $bean->? formatted code, but I have a query that requires a double table join and I am not able to convert that to the $bean->? method.

Can anyone help?

The idea is to have a field in my custom Events module show the total number of people related to the Event who have a status of Invited (custom field in the Event Participants module with a drop-down set of options, one of which is Invited) to the Event.  I have similar code for other status (Confirmed, Attended) but I show this one and will adapt to the others.

Excerpt of Code (in SQL query form) which does the job just fine (This is in the /modules/EvMgr_Evs/EvMgr_Evs.php file)
    $event_attendee_status = 'Invited';
    $event_attendee_status_count = 'num_invited';
    $query = 'SELECT COUNT(*) AS number_counted'.
    ' FROM evmgr_evs'.
    ' LEFT JOIN evmgr_evparts_evmgr_evs_c ON evmgr_evparts_evmgr_evs_c.evmgr_evparts_evmgr_evsevmgr_evs_ida ='.
    ' LEFT JOIN evmgr_evparts ON = evmgr_evparts_evmgr_evs_c.evmgr_evparts_evmgr_evsevmgr_evparts_idb'.
    ' WHERE'.
    ' evmgr_evparts_evmgr_evs_c.deleted = '0' AND'.
    ' evmgr_evparts_evmgr_evs_c.evmgr_evparts_evmgr_evsevmgr_evs_ida = '$event_id' AND'.
    ' evmgr_evparts.deleted = '0' AND'.
    ' evmgr_evparts.attendee_status = '$event_attendee_status';';
    $results = $this->db->query($query, true);
    $row = $this->db->fetchByAssoc($results);
    $this->{$event_attendee_status_count} = $calc_num;


There are three modules involved:
Custom Module EvMgr_EvParts (Event Manager Package with an Event Participants intermediary module)
Custom Module EvMgr_Evs (Event Manager Package with an Events module)

In the Events module, I want to populate the num_invited field with the number calculated.

Like I said, the above code works, but I am trying to 'clean up my act' and go the full $beans->? method, so would appreciate some help here.

I have read @jmertic 's post on using $beans code, but it does not show me a double table join example.

Any help appreciated.


(from Ramblin)