2

I am displaying a fee schedule using views and need to skip paid fee schedules. For that, I need to add following JOIN in views query using hook_views_query_alter().

LEFT JOIN ic_fee_schedule_payments AS FSP ON fee_schedule.fid = FSP.fid AND fee_schedule_students.uid = FSP.uid AND fee_schedule_instalments.iid = FSP.iid 

with added where clause as:

AND FSP.pid IS NULL

I have tried the following code in hook_views_query_alter(), but it does not work.

# In order to add a new table to join, you'll need to create a new views_join object.
    $join = new views_join;
    $join->construct('fee_schedule', 'fee_schedule_payments', 'fid', 'fid');
    $join1 = new views_join;
    $join1->construct('fee_schedule_students', 'fee_schedule_payments', 'uid', 'uid');
    $join2 = new views_join;
    $join2->construct('fee_schedule_instalments', 'fee_schedule_payments', 'iid', 'iid');
    #$join->extra = array(array('table' => 'fee_schedule_payments', 'field' => 'pid', 'operator' => 'IS', 'value' => 'NULL'));
    // $join is the views_join object created above
    $query->add_relationship('fee_schedule', $join, 'fee_schedule_payments');
    $query->add_relationship('fee_schedule_students', $join1, 'fee_schedule_payments');
    $query->add_relationship('fee_schedule_instalments', $join2, 'fee_schedule_payments');
    $query->add_where('fee_schedule_payments', 'fee_schedule_payments.pid', 'NULL', 'IS');

EDIT: Tried the other way but still does not work.

$join = new views_join;
    $join->construct('fee_schedule', 'fee_schedule_payments', 'fid', 'fid');
    $join->extra = array(array('table' => 'fee_schedule_payments', 'field' => 'uid', 'operator' => '=', 'value' => 'fee_schedule_students.uid'), array('table' => 'fee_schedule_payments', 'field' => 'iid', 'operator' => '=', 'value' => 'fee_schedule_instalments.iid'));
    $query->add_relationship('fee_schedule', $join, 'fee_schedule_payments');   
    $query->add_where('fee_schedule_payments', 'fee_schedule_payments.pid', 'NULL', 'IS');

Am I missing something or need to alter query in some other way?

3

I'm not 100% sure, but I think to add the "IS NULL" part, you may need spaces around "IS" such as:

$query->add_where('fee_schedule_payments', 'fee_schedule_payments.pid', 'NULL', ' IS ');

OR...maybe something using add_where_expression...

$query->add_where_expression(0, 'fee_schedule_payments.pid IS NULL');
0
0

In Drupal 8, the following seems to work:

$query->addWhere(0,'fee_schedule_payments.pid',NULL,'IS NULL');

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.