How can I use the current logged in member ID in a custom query using the query module?

Question

I have a query that I need to do that involves using the logged in member ID to limit the results. The following does not work, because the {member_id} variable is not parsed early enough.

{exp:query sql="SELECT ct.title AS title
            FROM exp_channel_titles AS ct
            WHERE ct.member_id = {member_id};"
                }

I've tried to pass {member_id} as an embed as well, but it outputs the actual curly braces again and does not work either:

{embed="members/.favorite-query" member_id="{member_id}"}

{exp:query sql="SELECT ct.title AS title
            FROM exp_channel_titles AS ct
            WHERE ct.member_id = {embed:member_id};"
                }

I then tried to create a global variable using the session_end hook. If I hard code the global variable, the query works, but it turns out that the $this->EE->session array is not available yet when using the session end hook, so I cannot use that method dynamically.

There is a thread on this topic here: http://ellislab.com/forums/viewthread/168569/#804745

I also tried creating a member_id value using a module tag, but again, the parse order is against me.

Am I overlooking any other options available here?

Answer 1

You can use the {exp:member:custom_profile_data} tag to access the member's profile information. By default it will use the currently logged-in member.

http://ellislab.com/expressionengine/user-guide/modules/member/index.html#custom-profile-data-tag

As this is a module tag this should now parse the {member_id} variable earlier than the {exp:query} tag:

{exp:member:custom_profile_data}

    {exp:query 
        sql="SELECT ct.title AS title
        FROM exp_channel_titles AS ct
        WHERE ct.author_id = '{member_id}'"
    }
    <p>{title}</p>
    {/exp:query}

{/exp:member:custom_profile_data}

Answer 2

Mo' Variables adds early parsed member variables. Once you've installed that the following query should work (you want to use author_id for the where condition, not member_id).

{exp:query sql="
    SELECT title
    FROM exp_channel_titles
    WHERE author_id = '{logged_in_member_id}'"
}