I hate to use SQL….

campaign.campaign_type='TEMPLATE' or
(
  (select count(*) from salesperson_assignment where
    salesperson_assignment.user_id = :user_id and
    (
      (salesperson_assignment.value_type = 'CAMPAIGN' and campaign.id = salesperson_assignment.value_id)
      or
      (
        salesperson_assignment.value_type = 'ADVERTISER' and salesperson_assignment.perm_scope = 'FULL' and
        campaign.advertiser_id = salesperson_assignment.value_id
      )
      or
      (
        salesperson_assignment.value_type = 'AGENCY' and salesperson_assignment.perm_scope = 'FULL' and
        (select agency_id from advertiser a where a.id = campaign.advertiser_id LIMIT 1) = 
        salesperson_assignment.value_id
      )
    )
  ) > 0
)

This site uses Akismet to reduce spam. Learn how your comment data is processed.

1. 就事论事来说，orm和sql复杂度是一样的，我假设你关注数据本身，当然写法会更elegant，而不是关注数据的availabiltiy，对declarative的语言，不能要求太多。

   Reply

2. 我是Object Relationship Mapping的拥护者，希望多用Object级别的操作来解决，也比较cache-friendly。。
   说起来前两天刚看到flickr的DBA写的一篇blog，有兴趣也可以看看
   http://mysqldba.blogspot.com/2007/11/when-starting-out-building-application.html

   Reply

3. 这条还好啦，都是bool判断的组合而已，逻辑不算复杂，只不过每个域都很长，拼起来ms比较难看而已。最恶心的sql应该是内嵌sql+表连接，又绕又难看，倒是可以出很多智力题。

   Reply

4. It’s not intuitive at all, hard to read and understand. I doubt even myself wouldn’t understand this SQL at the first glance after several weeks. But sub select has to be used sometimes…

   Reply

5. erm.. i think its poor format to have select statement inside your where clause..

   i learned that the hard way when i crashed my server working at bmo with a 200line query filled with select statements inside my where clauses for filtering purposes on the webpage..

   Reply