search

malcolm-kee / gatsby-source-mysql

Source plugin for pulling data into Gatsby from MySQL database
https://www.npmjs.com/package/gatsby-source-mysql
18 stars 10 forks source link

query returns less results than expected / plugin JOIN feature #9

Closed lucassilvagc closed 5 years ago

lucassilvagc commented 5 years ago

Hi! I'm using your plugin for a long time now and it's just awesome! Thanks for the amazing work.

I'm working with a friend (@guilhermelMoraes) on a Gatsby project that involves MySQL, and we're trying to retrieve some data using multiple SQL JOINS to properly retrieve names from different tables. If we run the query on MySQL Workbench, the query returns all the results as intended, more than 100+ results. When we apply the same query to gatsby-source-mysql on gatsby-config, it returns only one result from each parent data. So, when we filter these results using GraphQL, while it works as expected (since it returns at least one result and the result is in the expected results), it doesn't return all the results at once.

Since the query uses SQL JOINS, we've decided to use the recently added JOIN feature of the plugin, but still no results, IMHO due to multiple JOINS being used (does the plugin supports multiple JOINS in only one query, by the way?).

I'm attaching the EER Diagram we've made on Workbench.

Query: select l.numLoja as numLoja , concat(l.endereco, ', ',l.numero, ' - ', l.bairro, ' - ', l.municipio, ' - ', l.uf) as endereco , l.idTarifa as idTarifa , l.idPerecivel as idPerecivel , tn.idTabloide as idTabloideNormal , tn.descricao as tnDescricao , tni.img as tniImg , tni.categoria as tniCategoria , tni.formato as tniFormato , tni.dataInicio as tniDataInicio , tni.dataFinal as tniDataFinal from lojas l join tabloides tn on tn.idTarifa = l.idTarifa join tabloides_img tni on tni.idTabloide = tn.idTabloide where tn.dataInicio < current_timestamp() and tn.dataFinal >= current_timestamp()

Thanks in advance.

BR,

Lucas

malcolm-kee commented 5 years ago

Hi @lucassilvagc, thanks for create the issue. Multiple joins in a query should works by default as I just forward that to mysql package, which could handle that.

However, it's a bit hard for me to investigate if I can't reproduce the issue. Could you provide a sample query that could reproduce the issue using the example database comes with MySQL installation (sakila or world)?

lucassilvagc commented 5 years ago

Hi,

Sorry for the late reply! Turns out @guilhermelMoraes fixed the issue.

Thank you very much for the help.