[ElWesterno]

We develop Yii2 project for some time and we use default Yii2 GridView for creating overviews of data.
But client requires more and more things in overview and default Yii2 option isn’t sufficient enough.
Now the clients wants more and more customization of overviews which is very problematic. Every GridView needs it’s own DataProvider with hard to manage big SQL query. We have to create lots of joins in SQL for every table to fetch all related data, for every new thing there is too much code. We sometimes get SQL error “Memory allocation error: 1038 Out of sort memory” sometimes and we need to optimize our database queries to make it even work at all.

**Is there a better solution to create data grids/data tables in 2020 for PHP?** More robust? Quicker to develop? Easy to make it customizable?

New things we consider is Elasticsearch (we use that already to search and seems to be much easier to manage) and GraphQL (we use Vue components and seems that it can be used to create reactive data tables without using SQL and make it very easy to customize). Any comment on that?

[Flibbertygibbety22]

What a juicy problem to have – an actual, real-world issue, rather than a hypothetical one. Thanks for sharing it.

I don’t know Yii very well, I haven’t used it for about 10 years, but I don’t think it sounds like the root problem here. At a guess, it sounds like you’re pulling too much data back from the database, and then running out of memory when processing it. As such, swapping out for another library might not solve the problem by itself.

I don’t think GraphQL will help much. Ultimately, it still requires you to run one of those complicated SQL queries and somehow render the data. If you do it on the client-side, do the client machines have enough power?

Using Elasticsearch here would be like using a chainsaw to open your car door. It could work, but you’d have a lot of other new problems.

Options I can think of:

* Profile. Can you cut down on the amount of data you’re loading into memory? Are there any inefficient bits of code? Often, `foreach` loops over collections can end up with `O(log n)` type performance, using up all the memory.
* Scale vertically. Can you add more memory? Sounds like that wouldn’t solve the problem long term, but it might buy you time.
* Upgrade. Are you on the latest versions of PHP and Yii?
* Use generators, like `yield`.
* Optimise queries. Use things like `EXPLAIN` to see where indexes might help, or which queries you could rewrite.
* Denormalise. If you’ve got a lot of complicated SQL, could you (DBAs look away now) duplicate some columns so you don’t need to join? Alternatively, could you make query-friendly cache tables?

[secretvrdev]

Beside of the question which doesnt belog to this sub i have another one. Do you really like to define UI on the PHP side? Why?

Personally i like a very strict border between UI and PHP. It doesnt have to be an API but the UI is defined in html/twig and not in php.

[ExcellentHandle3068]

You can’t get away from fetching the data, that is most likely where your problem is, not your application layer.

[Sea_Stress1127]

Lead how to use Grid view, you don’t need no more… You need few joins normally.

Learn how to use the Search pattern in Yii

[russellvt]

Well, I’d probably be *more* looking at using something like d3js and some ajax/jQuery to fill it the gaps.

[ashe34h]

what does your query look like?

[raine1912]

Hello, we are facing similar issues as well, I will describe our issues and what we have considered:

1. Our database is relational(mariadb). To gather the information the client asks for we have to join many tables.

2. We want fast performance in most queries for the client.

3. We want the client to be able to somehow craft their own results without going too deep into the technical stuffs.

We have considered:

1. We think it’s necessary to replicate the database and have a version just for the purpose of searching. We are already using elasticsearch so it’s an option (albeit costly one). The idea is that using some kind of adapter or cron jobs we can populate elastic with the exact data necessary for filtering and displaying the grid.

2. We also found out about cubejs recently, not exactly for grid option but for charts and such. It allows defining tables you want to expose, allow writing custom join and queries, caching results
We have not done anything with it yet but I think there is some real potential there.

Back to the displaying of things, I think this should not have much to do with frontend code and not php.

[darkhorz]

disclaimer: I am not familiar with Yii2.

Have you looked into [MySql Views?](https://dev.mysql.com/doc/refman/8.0/en/views.html)

I have no experience using them myself, but it looks like something that could address your problem.

[Metrol]

I may be way off base as to what you’re looking for, but we’re using [Data Tables](https://datatables.net/) for the display side of things. I just pass the data in via JSON API call after the page loads. For more complex usage I would imagine you could also include information about which fields to show/hide and have the back end configure it on the fly.

Since it handles all the sorting, that part of the load can come out of the SQL.

I’m using PostgreSQL, so this may not apply to MariaDB. For the heavy time consuming queries I put them into [Materialized Views](https://www.postgresql.org/docs/current/rules-materializedviews.html) that I cron a refresh on in the background to keep them relevant. Also, it sounds like you may be dealing with quite a bit more data than I’m having to deal with.

When I stopped using MySQL way back when, heavy table joins were a bad thing on there. PostgreSQL seemed to do a lot better with bringing together lots of different tables quickly. It’s been a while, so that may not be the case any more.

[Author]

Posts