Cakephp SQL to Query Builder

can someone help me how to convert this to query builder . I am confuse why does Cakephp remove this features . I cant change my database structure because of thousand of records .


    SavingSub.code as holder,
    CONCAT(Member.lastName, ', ', Member.firstName,',', IFNULL(CONCAT(' ',Member.middleName), '')) as name,
    Member.code as member_code, as amount,
    Saving.joint_member_id as member2,


    savings as Saving LEFT JOIN
    saving_subs as SavingSub on SavingSub.saving_id = LEFT JOIN
    members as Member on = Saving.member_id LEFT JOIN

        SUM(IFNULL(Saving.interest,0)) + SUM(IFNULL(CASE SavingSub.type when '1' AND SavingSub.interest <= 0 then SavingSub.amount else 0 end,0)) + SUM(IFNULL(CASE SavingSub.type when '1' AND SavingSub.interest > 0 then SavingSub.interest else 0 end,0)) - SUM(IFNULL(CASE SavingSub.type when '0' then SavingSub.amount else 0 end,0)) - SUM(IFNULL(Saving.interest,0)) as total 
        savings as Saving 
        left join saving_subs as SavingSub On SavingSub.saving_id = 
        Saving.visible = true and 
        SavingSub.visible = true
      group by
    ) as TotalAmount on =

    Saving.visible = true and
    SavingSub.visible = true and
    Member.visible = true and 

   group by
  ) as Member
1 Like

@namatoj , can you help me with this ? or can you give me recommendation how can i make this work?

1 Like

You can save that query in a View and bake a table with the view name and use it as a normal table. with relations and everything. I would do that at least for the subquery join

@Johnshiva I think something is must wrong in your query. That’s why they remove your features. Just wait and watch what will be happened. :slightly_smiling_face:

How can you say that? my Cakephp 2.x is running smoothly .
If only 2.x can handle php 7 . i definitely wouldn’t upgrade to 4.x .
If you cant answer my question just ignore it . And watch will be happened

Question, why are you using a join with a subquery that does use the same table with the same conditions?

If the Member asociation is a OneToOne it should work the same as without subquery.

As for how to build the sum and case expr with query builder, you can see a post i did some time ago using count distinct with case

Done updating my query .
Gonna try this solutions tomorrow .

2.10 can handle PHP 7 just fine. But PHP 7 will only be around for so long…

Im working with php 7.3.27 with Cakephp 2.10.24 library .
Maybe im gonna stick with 2.x . Thank you

agree 2.x is the best and the speed can not be compared !