CiviCRM Community Forums (archive)

Hi All,

I'd like to be sure my understanding of this is correct.  Could someone please confirm?

My understanding is that when hook_civicrm_aclWhereClause is fired, CiviCRM is in the process of building a query that will look basically like this:

// $ACL_criteria: limiting criteria based on ACL access requirements
// $search_criteria: limiting criteria that point to search results or to a particular contact
// $UF_criteria: UF permissions limitations
$query = "SELECT x FROM y WHERE ($ACL_criteria) AND ($search_criteria) AND ($UF_criteria)";

And that the $where value in this hook will simply be dropped in as the value of $ACL_criteria in the query above.

Conceptually, this has helped me a lot in understanding what's going on in the hook, especially when I'm adding OR operators to the where clause.

So, am I close to right on this? Or could someone explain it a little better?

Many thanks,
Allen
 

Hi there,

I've just done the same task. Here is some code with comments that might help.

https://github.com/michaelmcandrew/gp/blob/master/modules/custom/gpew_local_party_access/gpew_local_party_access.module

See especially this comment.

    //
    // This was the tricky bit to work out.
    // contact_a.id must come as part of the preceding query, which we are now
    // appending to.
    //
    // A good strategy here is to work out in advance what sql you think would
    // work, and execute it against the database using phpmyadmin.
    //
    // For example, this seemed like a sensible query:
    // SELECT *
    // FROM `civicrm_contact` contact_a
    // LEFT JOIN `civicrm_value_gpew_party_information` partyMembershipTable
    // ON contact_a.id = partyMembershipTable.entity_id
    // WHERE partyMembershipTable.local_party_id = 22303
    //
    // The key thing to remember is that contact_a is part of the query which is
    // in the process of being created; contact_a is not part of what we are
    // doing, it's what came already.
    //

The breakthrough moment for me was when I figured that contact_a is coming from the system, and doesn't have to be supplied by me.

So a good starting point is to create a query and run it in phpmyadmin. The query you construct should be something like this:

SELECT *
FROM `civicrm_contact` contact_a
LEFT JOIN ...
ON contact_a.id = ...
WHERE ...

Then you can go from there and provide the where clause that will restrict the contacts to the ones you want the user to see.

@TwoMice: @anyone-else-out-there-with-aclWhereClause-knowledge:

I'd like to continue with this thread, I've found it useful and maybe others out there will as well.

I have a question: can you elaborate on how you implement an OR in the where-clause?

In my code I'm returning where clauses like this:

$where = "partyMembershipTable.local_party_id = $local_party_id";

This works as expected for a single integer value, which it usually is, for example 22212.

It doesn't work though when there are multiple local-party-ids.

I'm making $local_party_id into a string like "22212 OR partyMembershipTable.local_party_id = 22313"

This feels a bit primitive ... and indeed, it's not working ... in this case the ACL provides access to all contacts.

$left_join: LEFT JOIN civicrm_value_gpew_party_information partyMembershipTable ON contact_a.id = partyMembershipTable.entity_id
$where: partyMembershipTable.local_party_id = 22212 OR partyMembershipTable.local_party_id = 22313

I'd be grateful for your thoughts on how to improve this. Simply appending the OR on the end of $where isn't doing it properly, it seems.

Many thanks

Michael

Hi Michael,

Yes, OR can be tricky. 

1. For your specific use case, you can probably build an IN statement, like so:

partyMembershipTable.local_party_id IN (22212, 22313)

This will allow you to avoid the OR altogether. 

2. Sometimes you can't avoid an OR.  In this case you have to avoid polluting any existing WHERE clause and making it overly broad. Here is some code from a module I recently worked on. Maybe it will help:


  // Define our additional criteria here and store them in $critera
  $criteria = "(t1.column_1 = 'foo' OR t2.column_2 = 'bar')";

  // Civi will incorporate $where as the value of $ACL_criteria in this fashion:
  // $query = "SELECT x FROM y WHERE {$search_criteria} AND {$ACL_criteria} AND {$UF_permission_criteria}";
  // So we wrap our criteria with any existing $where into a parenthesized OR operation, thus achieving
  // a broader result than the original WHERE clause would get, while making sure
  // our OR operator doesn't pollute the entire WHERE clause and make it too broad.
  if (!empty($where) ) {
      $where = "($where OR $criteria)";
  }
  else {
    $where = "($criteria)";
  }


As a rule of thumb, wrapping OR operators in parentheses can be a good idea, because it allows you to contain their scope and treat them more as atomic criteria; leaving off the criteria increases your chance of making the entire WHERE clause too broad and causing it to return more records than it should.  This is the reason CiviCRM is giving access to so many extra contacts.

- Allen

P.S., Yeah, what lobo said. :-)