I'm trying to improve a drupal database query that works and looks like:
$get_key = $this->connection->query("SELECT * FROM tablekeys WHERE id IN ( SELECT MAX(id) FROM tablekeys WHERE key = 'System' GROUP BY ip) ORDER BY id DESC");
The first SELECT gets all fields in table keys and ORDERS them by id DESC
The middle query in the same table get's the higher MAX id where field key = 'System' and GROUPS by ip
Also need to get a count of each grouped ip as count
The expected result is a list of grouped ip's that was ordered by max id and then ordered by id desc.
Example tablekeys
id, ip, key
1, [login to view URL], System
2, [login to view URL], System
3, [login to view URL], System
4, [login to view URL], System
5, [login to view URL], System
7, [login to view URL], Log
8, [login to view URL], Log
9, [login to view URL], System
10, [login to view URL], System
11, [login to view URL], System
This should return:
11, [login to view URL], System, Count 4
10, [login to view URL], System, Count 3
3, [login to view URL], System Count 1
Like I said the $get_key query does it except for the count.
But I need it done via latest drupal database API and to be inserted in a form that displays the table. So it need to be:
$get_key = $this->connection->select('tablekeys', 'u')
->fields('u', ['id', 'ip', 'key'])
->ETC.
and include:
->extend('Drupal\Core\Database\Query\TableSortExtender')->orderByHeader($header_key)
as the last order and limit for pagination
->extend('Drupal\Core\Database\Query\PagerSelectExtender')->limit(20)
and finish with:
->execute()->fetchAll();
Hi,
I hope you doing well. I have 8 year experience in Web Development using PHP, Sql.
I saw the output you need. I can write it in a single query.
Could we have a chat and move forward?
Please give a chance to work with you.
Thanks
$15 USD in 1 day
4.0 (5 reviews)
2.7
2.7
3 freelancers are bidding on average $25 USD for this job