Pre-filter SQL query

[keianrad]

There is a client list 'app_client' that I record client J! user id, adviser id and office id in it. all other records in other tables are related to the clients list by a database join element.
right now I have to save J! user id, adviser id and office id in all records include client id that saved by database join.

Is it possible to use a SQL query in pre-filter to avoid save J! user id, adviser id and office id? and show them to the related users?

 

[keianrad]

Friendly bump.

 

[troester]

Not sure what you need exactly.

You can use queries in prefilters, e.g. assuming client_id is the dbjoin pointing to your app_client table (with columns id, user_id, ...)
something like

WHERE client_id IN
value:
SELECT id from app_client WHERE user_id = {$my->id}
Type: query

 

[cheesegrits]

Yeah, I'm not sure what you are asking.

Are you saying that rather than storing the user, adviser and office ID's in all those other tables, you'd like to fill them in based on the client ID, whenever a records is loaded?

If so, would you need to do this in list views, or only in form/details view?

-- hugh

 

[keianrad]

Yeah, I'm not sure what you are asking.

Are you saying that rather than storing the user, adviser and office ID's in all those other tables, you'd like to fill them in based on the client ID, whenever a records is loaded?

If so, would you need to do this in list views, or only in form/details view?

-- hugh

I need to store user id, adviser id and office id in a table "app_clients" when client makes and store client id in all other tables. then when I refer to them when I need to load a list.

for example: in documents list, I need to load all clients' documents that the adviser id is equal to login user or it is equals to office id of the login user

At present I am storing all user id, adviser id and office id in all records.

 

[cheesegrits]

for example: in documents list, I need to load all clients' documents that the adviser id is equal to login user or it is equals to office id of the login user

OK, then yes, you can probably do that with a pre-filter, using a subquery.

WHERE
Field: id
Condition: IN
Value:
SELECT id FROM app_documents AS d
LEFT JOIN app_clients AS c ON c.id = d.client_id
LEFT JOIN app_office AS o ON o.id = c.office_id
WHERE c.adviser_id = '{$my->id}'
OR o.user_id = '{$my->id}'
Type: query

That query may not be exactly right, but you get the idea - it should be a query that selects the id's of all documents that match the criteria you described.

-- hugh

 

[keianrad]

I used this code:

WHERE
Field: id
Condition: IN
Value: SELECT id FROM app_documents AS app_documents LEFT JOIN app_clients AS app_clients ON app_clients.user_id = '{$my->id}'

but I get this error:

Column 'id' in field list is ambiguous

 

[troester]

Try
...
Value: SELECT app_documents.id FROM app_documents AS app_documents LEFT JOIN app_clients AS app_clients ON app_clients.user_id = '{$my->id}'

 

[cheesegrits]

Ooops, yeah, if you join other tables, always fully qualify all the field names. My bad.

-- hugh