Basically what I'd like to do is limit the auto complete to only display users in which the messaging user has flagged. I made a view for this and tried to use the query from that to apply it to hook_privatemsg_sql_autocomplete_alter(). My SQL skills are non existent to say the least.

Heres my query from my created views...

SELECT users.uid AS uid,
users.picture AS users_picture,
users.name AS users_name,
flag_content.content_id AS flag_content_content_id,
DATE_FORMAT((FROM_UNIXTIME(flag_content_users.timestamp) + INTERVAL -14400 SECOND), '%Y%m%d%H%i') AS flag_content_users_timestamp_minute
FROM users users
INNER JOIN flag_content flag_content_users ON users.uid = flag_content_users.content_id AND flag_content_users.fid = 13
INNER JOIN users users_flag_content ON flag_content_users.uid = users_flag_content.uid LEFT JOIN flag_content flag_content ON users.uid = flag_content.content_id AND (flag_content.fid = 13 AND flag_content.uid = ***CURRENT_USER***)
WHERE (flag_content_users.uid IS NOT NULL) AND (users_flag_content.uid = 0)
ORDER BY flag_content_users_timestamp_minute DESC

With that I tried the following...

function fp_privatemsg_privatemsg_sql_autocomplete_alter(&$fragments, $search, $names) {

global $user;

$fragments['inner_join'] = 'INNER JOIN {flag_content} fc ON (fc.content_id = u.uid AND fc.fid = 13)';
$fragments['query_args']['where'][] = $user->uid;

}

Which obviously didn't work.

Comments

walker2238’s picture

walker2238 commented

Well I managed to limit this by the people who flagged the current user.

function fp_privatemsg_privatemsg_sql_autocomplete_alter(&$fragments, $search, $names) {
global $user;

  $fragments['inner_join'][] = 'INNER JOIN {flag_content} fc ON (fc.uid = u.uid AND fc.fid = 13)';
  $fragments['where'][] = 'fc.content_id = %d';
  $fragments['query_args']['where'][] = $user->uid; 
  
}

How can I switch this to show the current users flag?

litwol’s picture

litwol commented

What about fc.fid = 13

walker2238’s picture

walker2238 commented

Hey litwol,

fc.fid is the flag id so I'm not sure I understand what you mean.

litwol’s picture

litwol commented

Right, i think it should be replaced with %d where you currently have "13" and then fill that in using $fragments['query_args']

walker2238’s picture

walker2238 commented

Like so?

function fp_privatemsg_privatemsg_sql_autocomplete_alter(&$fragments, $search, $names) {
global $user;

  $fragments['inner_join'][] = 'INNER JOIN {flag_content} fc ON (fc.uid = u.uid AND fc.fid = %d)';
  $fragments['where'][] = 'fc.content_id = %d';
  $fragments['query_args']['where'][] = $user->uid; 
  
}

It doesn't work...

fc.content_id is for the users in which the current user flagged. So maybe i'm not using that correctly?

naheemsays’s picture

naheemsays commented

you will need to add an

  $fragments['query_args']['inner_join'][] = 13;

after the first line there.

What is the data structure and what are you trying to achieve? simple terms, forget the queries for a second. Which data in which tables are you try to to match?

I do not know the data structure, but are you not supposed ot join with flag_content_users and not flag_content?

walker2238’s picture

walker2238 commented

Well I guess that points out my knowledge with databases. I didn't use flag_content_users because the only flag table I found was flag_content.

Basically what I'm trying to do is limit to auto complete to display on the users that have been flagged by each user. I use flag to create a subscription type of relationship. So wanted to fill the auto complete with users that the current user is subscribing to.

I'm trying to match the flag_content table with the users table. Although by looking at the flag content table I guess I can use that table alone and it has the content_id which contains the uid that has been flagged, along with uid which is the user doing the flagging.

Does that make sense?

walker2238’s picture

walker2238 commented

Also heres the structure of the flag_content table.

fcid | fid | content_type | content_id | uid | timestamp
------------------------------------------------------

So I need to get all content_id's made by the current user (uid). Along with limiting it to only the fid(13).

berdir’s picture

berdir
Primary language German
Location Switzerland
commented

content_is the "remote uid" then?

Try that:

global $user;
$fragments['inner_join'][] = 'INNER JOIN {flag_content} fc ON (fc.content_id = u.uid AND fc.fid = %d AND fc.uid = %d)';
$fragments['query_args']['inner_join'][] = 13;
$fragments['query_args']['inner_join'][] = $user->uid;

As you can see, I joined on content_id, because you want to list the users the current user is connected to, you already know which one is the current user.

walker2238’s picture

walker2238 commented

I'm not sure what you mean by "remote uid". If you mean is it the uid of the flagged user then yes. If you mean is it the user who is doing the flagging then no.

The above doesn't work though..

berdir’s picture

berdir
Primary language German
Location Switzerland
commented

It is just join and not inner_join for the arguments, sorry..

global $user;
$fragments['inner_join'][] = 'INNER JOIN {flag_content} fc ON (fc.content_id = u.uid AND fc.fid = %d AND fc.uid = %d)';
$fragments['query_args']['join'][] = 13;
$fragments['query_args']['join'][] = $user->uid;
walker2238’s picture

walker2238 commented

Thanks again Berdir. I bought a MySQL book today. :)

berdir’s picture

berdir
Primary language German
Location Switzerland
commented
Status: Active » Fixed

This seems to be resolved :)

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.