sql - Convert MySQL view to Postgres -

i've inherited need convert production mysql db on postgres. has been handled without issue using simple sql statements table/function creation (using navicat generate semi-automated conversion), hitting problem converting complex view.

research suggests might due differences in how 2 dbs handle sub-queries (the statements), , perhaps it's syntax difference. business logic here unknown code-base has been inherited developer.

running following (using laravel migrations / php script):

select  parent.is_owner is_owner, parent.brand first_name, parent.id id, (select count(c.id)  campaigns c  where((        (c.user_id = parent.id)        or        (c.user_id = child.id)        )        ,        (c.campaign_status_id = 4) )) current_campaigns, (select count(c.id)     campaigns c             ((         (c.user_id = parent.id)         or (c.user_id = child.id)         )         , (c.campaign_status_id = 5) )) past_campaigns, (select count(c.id)     campaigns c             ((          (c.user_id = parent.id)          or (c.user_id = child.id))          , (c.campaign_status_id = 2)         ))     pending_campaigns, (select count(c.id)        campaigns c     ((             (c.user_id = parent.id)             or (c.user_id = child.id)             )             , (c.invoice_status = '1')         ))     past_invoices ((users parent left join campaigns mc on      ((parent.id = mc.user_id)))     left join users child on ((child.parent_owner = parent.id)     )) (     (parent.is_owner = 1)     or (child.is_retailer = 1) ) group parent.id order parent.brand 

... triggers error

sqlstate[42803]: grouping error: 7 error:  subquery uses ungrouped column "child.id" outer query   line 1: ...c (((c.user_id = parent.id) or (c.user_id = child.id)) ... 

can suggest how format postgres runs sub-queries?

btw, php code used here in laravel migration script is:

...

db::unprepared("create view client select parent.is_owner is_owner,parent.brand first_name,parent.id id    ,(select count(c.id) campaigns c (((c.user_id = parent.id) or (c.user_id = child.id)) , (c.campaign_status_id = 4))) current_campaigns    ,(select count(c.id) campaigns c (((c.user_id = parent.id) or (c.user_id = child.id)) , (c.campaign_status_id = 5))) past_campaigns    ,(select count(c.id) campaigns c (((c.user_id = parent.id) or (c.user_id = child.id)) , (c.campaign_status_id = 2))) pending_campaigns    ,(select count(c.id) campaigns c (((c.user_id = parent.id) or (c.user_id = child.id)) , (c.invoice_status = '1'))) past_invoices ((users parent left join campaigns mc on((parent.id = mc.user_id))) left join users child on((child.parent_owner = parent.id))) ((parent.is_owner = 1) or (child.is_retailer = 1)) group parent.id order parent.brand;"); 

update, fixed:

brilliant. input here all.

the solutions @patrick , @erwinbrandstetter both work. favour patrick's here role in convert system "as-is". there may scope refactor in future, @ stage feel risky mess (or improve) else's duct tape solution (i.e code-base seems overly complex in places, no sign of documentation, , i'm reluctant poke around or attempt core improvement without more background info on business logic). suspect parts of model may need overhauled anyway, [sic]-fix favoured here.

i suspected click-jiggery might have generated original query... trying give original dev benefit of doubt , assume there business pressure called quick (i.e mousy) turn-around. complex sql not strong suit i'm glad instinct correct, query being unnecessary complex begin with. perhaps view unplanned bolt-on -not designed in first place. wise or not, i'd have tried hit problem orm based approach.

i'm on project last minute, running cleanup re-launch (original dev "let go"), working undocumented code-base full of unknown functionality. running paratrooper were. thankfully, view issue appears last piece of puzzle. thank :-)

oh my, oh my. developer had tic in right ring finger, no doubt, because statement had no less 74 parentheses. here how can 8 parentheses , 14 lines instead of 54:

select    parent.is_owner is_owner,   parent.brand first_name,   parent.id id,   sum(case when c.campaign_status_id = 4 1 else 0 end) current_campaigns,   sum(case when c.campaign_status_id = 5 1 else 0 end) past_campaigns,   sum(case when c.campaign_status_id = 2 1 else 0 end) pending_campaigns,   sum(case when c.invoice_status = '1' 1 else 0 end) past_invoices, users parent left join users child on child.parent_owner = parent.id left join campaigns c on c.user_id = parent.id or c.user_id = child.id parent.is_owner = 1 or child.is_retailer = 1 group parent.is_owner, parent.brand, parent.id order parent.brand;

no sub-selects means code run faster boot. wolph mentioned in comment, every column in select list not included in aggregate function must appear in group by clause, specified sql standard, blissfully ignored mysql.

the sub-selects avoided using case construct: conditional expression evaluation in column list. note repetitive clause of filtering in sub-selects performed join clause, 1 relevant column in campaigns evaluated per column in main query. issuing 1 or 0 case statement , wrapping in sum() function nifty trick multiple, distinct counts in single query.

as wolph noted in comment below answer, clause

sum(case when c.campaign_status_id = 4 1 else 0 end) current_campaigns 

can written more succinctly as

sum((c.campaign_status_id = 4)::integer) current_campaigns 

this may faster case statement given boolean integer cast in c language in postgresql written not require operation (a boolean either 1 or 0 in c). legibility less (not mention use of twice many parentheses!).