In my project user can created by profile2 module. When user is created i am assigning some role,initially user is block state. I have another user role ie.user app rover who can approved these user and send activation link for active user.

I am using Mail Logger module for saving sending mail Now i have created a view for user unapprove list

    SELECT users.name AS users_name, users.uid AS uid, users.mail AS users_mail, 
    profile_users.pid AS profile_users_pid, users.created AS users_created, 'profile2' AS 
    field_data_field_cc_first_name_profile2_entity_type, 'profile2' AS 
    field_data_field_cc_address_office_profile2_entity_type, 'profile2' AS 
         {users} users
         LEFT JOIN {profile} profile_users ON users.uid = profile_users.uid AND 
         profile_users.type IN ( 'company', 'goverment_profile' )
         WHERE (( (users.status = '0') AND (users.login = 0) AND (profile_users.type IN 
            ('company', 'goverment_profile')) ))
            ORDER BY users_created DESC
            LIMIT 10 OFFSET 0

Hear i want to add sub-query in where condition user those already send activation link who will not list in view.

My other table is mail_logger.

Is there any module by which i can use sub-query in where condition?

1 Answer 1


Just ran into the same question, here is what I found:

You can add a sub query to your view using the hook_views_query_alter hook in a custom module (documentation). Inside the module you should add something like the following:

function your_module_views_query_alter(&$view, &$query) {
  if ( $view->current_display == '[display in question]'){
    $query->add_where_expression([group], [sub query]);

documentation for $query->add_where_expression. You can add whatever where clause you need using this function.

hook_views_query_alter should go in your_module.views.inc. Make sure you declare hook_views_api in your_module.module, otherwise views wont know to look for the file. For example:

function your_module_views_api() { // declare your views api # here
  return array(
    'api' => 3,

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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