Security Filter vs. __sql__

[ADobkin]

In the context of limiting records based on the logged in user, what is the difference between using the setSecurityFilter() function in an init() delegate class method versus using the __sql__ delegate class method without the security filter? Is there a performance or security benefit to one or the other?

I have a case where I am trying to filter based on a many-to-many relationship. It seems I must do this in the __sql__ method with a left join, and it appears to work either way. Just wondering if I am missing something....

Thanks,
Alan

[shannah]

Really you should be using both in this case. __sql__ should not provide any filtering. It should only append columns to the set. I recently attached a portion of an unfinished manual that describes this restriction:
viewtopic.php?f=4&t=6650

In cases where you need to filter on a related table, you should use __sql__ to add columns as necessary to do a filter. Then you should use the security filter on those appended columns.

[ADobkin]

Okay, thanks. That manual looks great, by the way! I look forward to seeing the rest of it. Let me know if there is anything I can do to contribute or help.

The way you described it is actually how I am doing it, i.e. with a left join in the __sql__ in conjunction with setSecurityFilter in the init. But while I was testing, I noticed that I could use a WHILE clause in the __sql__ to achieve the same thing as the security filter, and it appeared to work the same way with less code. My curiosity just led me to wonder why it is recommended to use the security filter instead.

[shannah]

While I can't give a tangible example off the top of my head of where filtering via the __sql__ method will cause problems, most parts of Xataface were developed with the assumption that the query produced by __sql__ should be the same as the default query (select * from foo) in cardinality. If this assumption is incorrect, it is possible that some parts of the application may produce unexpected results.

[ADobkin]

Good enough for me! Thanks.