Questions around some SQL queries

DecSoft NeoPlugins pone sus proyectos de VisualNeo en otro nivel con +1.000 nuevas acciones para sus apps.

¡Eche un vistazo a nuestro canal de YouTube y suscríbase para recibir notificaciones sobre nuevos vídeos!

DecSoft App Builder integra un asistente de Open AI para ayudarle mientras desarrolla sus apps.

DecSoft HTML Compiler es fácil de usar: personalize su proyecto y compile sus apps y sus juegos rápidamente.

Cree modernas apps y juegos HTML a otro nivel haciendo cosas que incluso las modernas apps y juegos HTML no pueden.

DecSoft HTML Compiler incluye un conveniente Compilador por línea de comandos para compilar sus apps desde la consola.

Cree apps y expanda sus capacidades usando el lenguaje más popular del mundo: Javascript.

Ahorre tiempo, recursos y dinero creando apps para Android, iOS y más con la misma base de código.

Cree apps multiplataforma con DecSoft App Builder, incluyendo Android e iOS, con la misma base de código.

Los NeoPlugins de DecSoft tienen versiones "designtime" y "runtime" para reducir el tamaño de sus apps.

DecSoft App Builder incluye un conveniente Compilador por línea de comandos para compilar sus apps desde la consola.

Ahorre dinero comprando nuestros paquetes de NeoPlugins. ¡Cinco, Diez e incluso nuestra colección entera a un precio increíble!

Los plugins oficiales de Apache Cordova se incluyen de serie y hay cientos de ellos disponibles para su uso.

Compile, convierta sus modernas apps y juegos HTML en programas ejecutables sin dependencias para Microsoft Windows.

¡Eche un vistazo a algunas de preguntas más frecuentes sobre nosotros y nuestros productos!

Diseñe para pantallas pequeñas y distribuya también (sin cambios) en dispositivos como tablets.

Amin Mousavi

Hi David, I have got stock in optimising my query somewhere I think you could advise. I am trying to run the below query on the API you have provided for AB Database example:

CREATE VIEW temp AS (SELECT * FROM tb1);
SELECT * FROM temp AS t1 JOIN temp AS t2 ON t1.id = t2.id;

I use Database::getResults in the API to run the SELECT part that works fine, but I had not been able to run the CREATE VIEW as of yet. I tried

Database::beginTransaction();
Database::exec($query)

and a few other pdo functions to execute the CREATE VIEW but had not luck. I appreciate if you advise. Regards,

DecSoft

Hello Amin,

If I am not wrong, to create the VIEW you must use the "Database::exec()" method. So the only thing that I see rare is that if you use "Database::beginTransaction"()", after execute the query, you must execute "Database::commit()". Maybe you no need to use the "begin transaction" at all?

Can this help you in some manner?

Purchase one or more licenses of our products if they are useful to you!

Amin Mousavi

Hi David, During the debugging I figured that

Database::beginTransaction();
Database::exec($query)

Was infact working correctly, and my view was created, the reason I get the errors is that I am trying to replicate "WITH" in MySQL and still have no luck. Thanks a million

DecSoft

Hello Amin,

Then I am not sure if can understand. First of all, probably you want to create the view over and over again: once you create the view (for example with a database manager like PhpMyAdmin), what you must to do is to execute the appropriate SQL query. You talk about errors, Amin, but can you please refer that errors here?

Purchase one or more licenses of our products if they are useful to you!

Amin Mousavi

Thanks a lot David for always going the extra mile, I did not expect you to look into that because it is totally irrelevant to AB and the APIs I got from it. But any how to clear things up. I am running a query like below:

SELECT A,B FROM (SELECT * FROM T1) AS Q1 JOIN SELECT B,C FROM (SELECT * FROM T1) AS Q2 ON Q1.B=Q2.B;

Because both sides of JOIN are running the same query (SELECT * FROM T1) it will be more efficient if we can buffer it somewhere first time it is run and then reuse it the second time. This in a simplified example and when the reused query is something with a few joins and subqueries and data has millions of records it will make a measurable difference. In SQLSERVER and ORACLE this issue can be taking care of by TEMPORARY tables and/or WITH but MySQL temporary tables does not help with this. WITH is available in MySQL 8 apparently but my server's SQL version is 5.6 and does not support WITH so I am stuck doing my best to replicate these using views, memory engines and technically anything that has the ability to speed up my query in a similar fashion. Don't worry about it though, I will find a way for it ;)

DecSoft

Hello Amin,

I am not an expert in SQL, so, I am not sure if can help you in the best way, but at least I can always try it. :-)

Purchase one or more licenses of our products if they are useful to you!

Todo el mundo puede leer el foro de soporte de DecSoft para aprender del mismo, sin embargo, sólo los clientes de DecSoft pueden abrir nuevos hilos. Compre una o más licencias de productos de DecSoft y obtendrá este y otros beneficios.