KoolReport's Forum

Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines

Tour / country multiple to multiple relation - reports duplicates rows for each country #942

Open paulo opened this topic on on Jun 24, 2019 - 3 comments

paulo commented on Jun 24, 2019

Hi All, I have a table for tour, and one for country, It is a multi multi relation, so we have a middle table called 'Country_Tour' with both ids. I did join and present the information using a DataTables use \koolreport\datagrid\DataTables;

because I like the option to 'search' in this table. However, each country is shown in a separate row. Is there a (easy) way of combining the countries to show one tour per row, and the countries for that tour separate by comma?

This is how I am trying now: $allCountries = $this->src('mysql')->query("select id,name as CountryName from countries");

    $countriesTours = $this->src('mysql')->query("select id,country_id, tour_id  from country_tour");
    $joinCountries = new Join($countriesTours,$allCountries,array("country_id"=>"id"));

    $allTours  = $this->src('mysql')->query("select tours.id as tourid,concat_WS(Prefix, ' ' ,tours.number, ' ',tours.name) as tourname, startDate, endDate, insurance_number, S.name as planner
    from tours JOIN statuses on statuses.id= tours.status_id 
      left join staff as S on S.id = tours.Planner
    WHERE statuses.status = \"active\" and tour_status !=\"0\"");
    $join = new Join($allTours,$joinCountries,array("tourid"=>"tour_id"));
    $join->pipe($this->dataStore('searchabletours'));

thank you very much Paulo

KoolReport commented on Jun 26, 2019

Your question give us an interesting idea for grouping. I have told dev.team and let see what they can come up with. I will keep you update.

paulo commented on Jun 26, 2019

thank you !

paulo commented on Aug 29, 2019

Everyone, if you are trying to do something similar , you can use GROUP_CONACT, join and subquery to perform this action; LEFT join (SELECT tour_id, GROUP_CONCAT(name SEPARATOR ', ') as countryname FROM ETProdDB.country_tour

		left join countries AS country on country.id = country_tour.country_id
		GROUP BY  tour_id) as countries on countries.tour_id = tours.id

Report will only display the 'countryname' as if it was a field

Build Your Excellent Data Report

Let KoolReport help you to make great reports. It's free & open-source released under MIT license.

Download KoolReport View demo
help needed
suggestion

None