Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Not bringing back hasMany Association #16

Closed
joakim-emagine opened this issue Jan 23, 2019 · 4 comments
Closed

Not bringing back hasMany Association #16

joakim-emagine opened this issue Jan 23, 2019 · 4 comments

Comments

@joakim-emagine
Copy link

The plugin works great when for belongsto associations but does not work correctly for hasMany associations.

Getting either a column not found or field must be included in list.
Column not found: 1054 Unknown column 'TransactionPayments.transaction_id' in 'field list'

As you can see below the 'TransactionPayments' table is not included in the sql statement.

I have left out some columns because of length.

        $this->DataTables->createConfig('Transactions')
               ->column('Transactions.transaction_date', ['label' => 'Date'])
                ->column('Contacts.name', ['label' => 'Customer name'])
                ->column('Locations.name', ['label' => 'Location'])
                ->column('Transactions.total_paid', ['label' => 'Total paid', 'database' => false])
                ->column('Transactions.total_remaining', ['label' => 'Total remaining', 'database' => false])
                ->column('action', ['label' => 'Actions', 'database' => false])
                ->queryOptions([
                    'contain' => ['Contacts' => ['fields' => ['Contacts__name' => 'Contacts.name']],
                                 'TransactionPayments' => ['fields' => 
                                                ['TransactionPayments__amount' => 'TransactionPayments.amount',
                                                 'TransactionPayments__transaction_id' => 'TransactionPayments.transaction_id',
                                                ]],
                                 'Locations' => ['fields' => ['Locations__name' => 'Locations.name']]
                                 ],
                    'fields' =>  [
                        'Transactions.id',
                        'TransactionPayments.transaction_id'
                        'TransactionPayments.amount',
                    ],
                    'conditions' => [
                        'Transactions.business_id' => $business_id,
                       // 'Transactions.id' => 'TransactionPayments.transaction_id',
                        'Transactions.type' => 'sell',
                        'Transactions.status' => 'final'
                        ]
                    ]);

SQL statement

SELECT Transactions.id AS `Transactions__id`, Transactions.transaction_date AS `Transactions__transaction_date`, Transactions.is_direct_sale AS `Transactions__is_direct_sale`, Transactions.invoice_no AS `Transactions__invoice_no`, Transactions.payment_status AS `Transactions__payment_status`, Transactions.final_total AS `Transactions__final_total`, TransactionPayments.transaction_id AS `TransactionPayments__transaction_id`, TransactionPayments.amount AS `TransactionPayments__amount`, Contacts.name AS `Contacts__name`, Locations.name AS `Locations__name` FROM im_transactions Transactions LEFT JOIN im_contacts Contacts ON Contacts.id = (Transactions.contact_id) INNER JOIN im_business_locations Locations ON Locations.id = (Transactions.location_id) WHERE (Transactions.business_id = :c0 AND Transactions.type = :c1 AND Transactions.status = :c2) ORDER BY Transactions.transaction_date desc LIMIT 10 OFFSET 0
@joakim-emagine
Copy link
Author

A workaround for anyone trying this, I had to use:

$results->select(['total_paid' => $results->func()->sum('TransactionPayments.amount')])
    ->leftJoinWith('TransactionPayments')
    ->group(['TransactionPayments.id']);

in the datatables/configname.ctp

In my case i did an aggregate function sum().

@allanmcarvalho
Copy link
Owner

allanmcarvalho commented Jan 23, 2019

Try this...
Use a simple "contain". Eg.:
'contain' => ['Contacts' => ['Cities'], 'TransactionPayments', 'Locations'],

By default, the plugin get only the fields that is need. You must declare all on '->column()' or '->databaseColumn()' methods. If you're using it this way you'll get all associations. The second method is for get only database information, but not create a datatable column.

Even using 'contain', you will need to tell which database cols you want to use if there is no column in the 'datatables' that uses it.

Quick example:

$this->DataTables->createConfig('Transactions')
               ->column('Transactions.transaction_date', ['label' => 'Date'])
                ->column('Contacts.name', ['label' => 'Customer name'])
                ->column('Locations.name', ['label' => 'Location'])
                ->column('database_disabled_I_can_write_anything', ['label' => 'Total paid', 'database' => false])
                ->column('Transactions.total_remaining', ['label' => 'Total remaining', 'database' => false])
                ->column('action', ['label' => 'Actions', 'database' => false])
                ->databaseColumn('Transactions.id')
                ->databaseColumn('TransactionPayments.transaction_id'')
                ->databaseColumn('TransactionPayments.amount'')
                ->queryOptions([
                    'contain' => ['Contacts',  'TransactionPayments', 'Locations'],
                    'conditions' => [
                        'Transactions.business_id' => $business_id,
                       // 'Transactions.id' => 'TransactionPayments.transaction_id',
                        'Transactions.type' => 'sell',
                        'Transactions.status' => 'final'
                        ]
                    ]);

@joakim-emagine
Copy link
Author

This works! thanks much.

@thejoelinux
Copy link
Contributor

Hello, sorry to bring it back, but i really don't get it.

Let's say you have a Customers list, and you want a column with the number of Invoices for the customer or, maybe, the sum of all the invoices (considering the amount of the invoice is in the Invoices table, not in some related table).

We have an hasMany association between Customers and Invoices.

DataTables configuration is something like:

$this->DataTables->createConfig('Customers')
    ->column('Customers.name', ['label' => 'Name'])
    ->queryOptions(['contain' => 'Invoices'])

Past this, I'm not able to determine what is necessary to display, count, and/or sum this row.

I think it really misses some explicit documentation on the subject. I would like to write it.

I'll try to set up a small example project to show what I'm trying to do.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

3 participants