Cakephp SQL to Query Builder

1

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 .

SELECT * FROM (

  SELECT
    Saving.id,
    Saving.code,
    Saving.ssa,
    Saving.saving_type_id,
    SavingSub.code as holder,
    SavingSub.rate,
    Saving.terms,
    CONCAT(Member.lastName, ', ', Member.firstName,',', IFNULL(CONCAT(' ',Member.middleName), '')) as name,
    Member.code as member_code,
    TotalAmount.total as amount,
    Saving.joint_member_id as member2,
    Member.address

  FROM

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

    (
      SELECT
        Saving.id,
        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 
      FROM 
        savings as Saving 
        left join saving_subs as SavingSub On SavingSub.saving_id = Saving.id 
      where 
        Saving.visible = true and 
        SavingSub.visible = true
      group by
        Saving.id
    ) as TotalAmount on TotalAmount.id = Saving.id
    
   WHERE

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

   group by Saving.id
  ) as Member
1 Like
2

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

1 Like
3

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

4

@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:

5

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

6

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

7

Done updating my query .
Gonna try this solutions tomorrow .
Thanks.

8

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

9

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

11

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