0

This is an index (of module services) I created a resource that index books on a home page of a mobile app( this is not the most important in my question) I want to get books that have same tid (from table taxonomy) and put them on an array ($livres) at this time when I return $livres it's ok, books are grouped well and no duplications, but after putting them on array $livres, i want to put the $livres on the array $sections, sections contain the "header" that is the name of the section, and the array $livres of every section : the result i want is : [ { 'section' : 'A', 'livres':[{'id_book' : '1','title':'great book'},{'id_book' : '2','title':'great book too'}]},{'section':'B','livres':[{...ect

function my_module_home_index(){
$sections = array();
$livres = array();

$home_livre = db_query("SELECT tax.name as section,field_categorie_tid as tid,field_title_value as titre ,field_prix_value as prix ,t.entity_id FROM {field_data_field_title} as t
    join {field_data_field_prix} as pr on pr.entity_id = t.entity_id
    join {field_data_field_categorie} as cat on cat.entity_id = t.entity_id
    left join {taxonomy_term_data} as tax on tax.tid = cat.field_categorie_tid
    ")->fetchAll();

foreach($home_livre as $book){
    if(!isset($livres[$book-> tid]))
    $livres[$book-> tid] = array();


    $livres[$book->tid][]= array(
        'id_livre'=>$book-> entity_id,
        'titre' => $book-> titre,
        'prix'=> $book-> prix
    );
    $sections[]= array(
        'categorie'=> $book-> section
        'livre'=> $livres[$book->tid]
    );
}       
return $sections;   
  }

This is a part of what postman returns :

{
"categorie": "A",
"livre": [
  {
    "id_livre": "109",
    "titre": "F",
    "prix": "4"
  }
]
  },
{
  "categorie": "A",
  "livre": [
    {
     "id_livre": "109",
    "titre": "F",
    "prix": "4"
  },
  {
    "id_livre": "110",
    "titre": "G",
    "prix": "43"
  }
 ]
},
{

"categorie": "A",
"livre": [
  {
    "id_livre": "109",
    "titre": "F",
    "prix": "4"
  },
  {
    "id_livre": "110",
    "titre": "G",
    "prix": "43"
  },
  {
    "id_livre": "111",
    "titre": "H",
    "prix": "6"
  }
  ]
 },

As you can see; Book number 109 appears every iteration of foreach, how to avoid that ?

This is what return $livres shows (just a part, whent tid : 41, array is grouping all books with this tid so it's ok):

"41": [
{
  "id_livre": "109",
  "titre": "F",
  "prix": "4"
},
{
  "id_livre": "110",
  "titre": "G",
  "prix": "43"
},
{
  "id_livre": "111",
  "titre": "H",
  "prix": "6"
},
{
  "id_livre": "115",
  "titre": "L",
  "prix": "4"
},
{
  "id_livre": "118",
  "titre": "O",
  "prix": "5"
}
  ]
  }

Thank you

EDIT : this is the result postman returns when i "range" the query with 0,5 : i want 5 books (livres) in every sections, did anyone knows how to range the first array (not the second one that contains livres)

"response": [
{
  "section": "roman policier",
  "id_section": "31",
  "livres": [
    {
      "couverture": "http://localhost/drupal/sites/default/files/Etude_en_rouge.jpg",
      "id_livre": "113",
      "titre": "Une étude en rouge",
      "auteur": "Arthur Conan Doyle",
      "prix": "8",
      "categorie": "30",
      "section": "31"
    },
    {
      "couverture": "http://localhost/drupal/sites/default/files/fables.jpg",
      "id_livre": "119",
      "titre": "Fables",
      "auteur": "Jean de La Fontaine",
      "prix": "0",
      "categorie": "30",
      "section": "31"
    },
    {
      "couverture": "http://localhost/drupal/sites/default/files/couverture-livre_2.png",
      "id_livre": "120",
      "titre": "lire_latin",
      "auteur": "ecri_latin",
      "prix": "33",
      "categorie": "30",
      "section": "31"
    }
  ]
},
{
  "section": "roman drole",
  "id_section": "32",
  "livres": [
    {
      "couverture": "",
      "id_livre": "114",
      "titre": null,
      "auteur": null,
      "prix": "444",
      "categorie": "30",
      "section": "32"
    }
  ]
}
2

You could try this:

function my_module_home_index(){
  $sections = array();

  $home_livre = db_query("SELECT tax.name as section,field_categorie_tid as tid,field_title_value as titre ,field_prix_value as prix ,t.entity_id FROM {field_data_field_title} as t
    join {field_data_field_prix} as pr on pr.entity_id = t.entity_id
    join {field_data_field_categorie} as cat on cat.entity_id = t.entity_id
    left join {taxonomy_term_data} as tax on tax.tid = cat.field_categorie_tid
    ")->fetchAll();

  foreach($home_livre as $book){
    if (!isset($sections[$book->section])) {
      $sections[$book->section] = array(
        'categorie' => $book->section,
      );
    }

    $sections[$book->section]['livre'][] = array(
      'id_livre' => $book->entity_id,
      'titre' => $book->titre,
      'prix' => $book->prix,
    );
  }

  // The above will set up $sections as an associative array, but you need
  // to return an indexed array, so run it though array_values()
  return array_values($sections);
}

Basically, The first book that comes along with category "A" will create a new child the in $sections array with a key of "A". The book will then be nested in that child. If another book is found with category "A" then instead of creating a new category child, the book is nested inside of the same "A" category.

Note that I've not tested this code live so might need some tweaking

  • Hi Paul, Great answer it works Thank you very much – Mitch Aug 11 '15 at 10:43
  • Hi Paul, i'm facing a new "issue" with this same query, now i have a query that returns an array into another one, imagine i want to range result between 0 and 10 (i used db_query_range) and when i add 0,10 i want every section contains 10 books (livre) but now in my result i've got : 10 books for ALL of the sections (see what i've edited in my question please paul) – Mitch Aug 24 '15 at 10:14
  • I think you'll have trouble doing this with a single query, unless you don't limit the number of results at all in the query and limit them in PHP in your loop, but if the data set gets large then that would have significant performance implications. There are some approaches you could try at xaprb.com/blog/2006/12/07/… but I think I'd recommend having two queries; In the first you'd get the sections and loop through the results. For each section run a separate query to get the books (with the limit clause). – Paul Querol Aug 24 '15 at 11:31
  • Yes Paul it's more logical to make two queries that's right, i'm working on it. Thank you – Mitch Aug 24 '15 at 12:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.