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

thank you !

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