Jump to:

3434 Posts in 1026 Topics by 866 members

Template Questions

SilverStripe Forums » Template Questions » Arraylist and queries

Moderators: martimiz, Sean, biapar, Willr, Ingo, swaiba, simon_w

Page: 1
Go to End
Author Topic: 783 Views
  • Drumstick
    Avatar
    Community Member
    20 Posts

    Arraylist and queries Link to this post

    Hi

    Maybe this question is old but I didn't find an answer. I have 3 DB querys, one is for a product the other for picutres many_many relation. The result is ok, but now I push them in a Arraylist and it loops 4 records, but it should be just one record. When I make two Arraylists, one for the product, the other for the pictures, then I have the problem that I can't loop in a loop, but the record of the prodcut is one and that is correct. But I have to loop in a table, and there you can click on a result and then it popups a dialog box. So I have to loop my product result and then the images in the product loop. But this does not work. How can I do this?

    Does somenone know? Or what do I wrong. The result of the db queries are correct, so there can't be a mistake in my opinion. The problem is in the merge of the several results and the loop in a loop.

    Thanks a lot for help.

  • Willr
    Avatar
    Forum Moderator
    5489 Posts

    Re: Arraylist and queries Link to this post

    then I have the problem that I can't loop in a loop

    Why can't you loop inside a loop? So you images link to a product through a relation. Lets say your model looks like

    class Product extends DataObject {

    static $many_many = array(
    'Images' => 'ProductImage'
    );
    }

    And you get your list of products for the page like something

    function getProducts() {
    return Product::get();
    }

    To get your images in the template you would then have something like

    <% loop Products %>
    <% loop Images %>
    $Filename
    <% end_loop %>
    <% end_loop %>

    If you post a link to your code we can help in more detail.

  • Drumstick
    Avatar
    Community Member
    20 Posts

    Re: Arraylist and queries Link to this post

    Hi Willr

    Thanks a lot for answer.

    I have a form in which you can choose your product, you can choose your place code and the distance...now I need as result the products from the places in this distance....and the products have pictures

    For the products I have a drop down solution with jquery, so you don't see a map there because of that..but it works.

    Here is my code:

    public function ProductsearchForm() {

    $fields = new FieldList(

    DropdownField::create(
    'Productcategory',
    'Produktkategorie'
    ),

    DropdownField::create(
    'Productsubcategory',
    'Subkategorie'
    ),
    DropdownField::create(
    'Product',
    'Produkt'
    ),

    TextField::create(
    'PLZ',
    'PLZ'
    ),
    TextField::create(
    'Place',
    'Ort'
    ),
    TextField::create(
    'Distance',
    'Umkreis km'
    )

    );

    $validator = new RequiredFields(
    'Productcategory',
    'Distance'

    );

    $actions = new FieldList(FormAction::create("submit")->setTitle("Suchen"));

    return new Form($this, 'ProductsearchForm', $fields, $actions,$validator);

    }

    public function submit(array $data, Form $form) {

    $plz = $data['PLZ'];
    $ort = $data['Place'];
    $distance = $data['Distance'];
    $product = $data['Product'];

    $sqlQuery = new SQLQuery();
    $sqlQuery->setFrom('Customerproduct');
    $sqlQuery->addWhere("Product = '$product'");
    $sqlQuery->addLeftJoin('Customer', '"Customerproduct"."CustomerID" = "Customer"."ID"');
    $sqlQuery->addWhere('PLZ = '.$plz);

    $rawSQL = $sqlQuery->sql();

    // Execute and return a Query object
    $result = $sqlQuery->execute();

    $lng = "";
    $lat = "";
    $userid = array();
    $productid = array();
    // Iterate over results
    foreach($result as $row) {
    $lng = $row["Longitude"] / 180 * M_PI;
    $lat = $row["Latitude"] / 180 * M_PI;
    $userid[$row['ID']] = $row['ID'];

    };

    $sqlQuery = new SQLQuery();
    $sqlQuery->addFrom('Customer');

    $sqlQuery->selectField("(6367.41*SQRT(2*(1-cos(RADIANS(Customer.Latitude))*cos(".$lat.")*(sin(RADIANS(Customer.Longitude))*sin(".$lng.")+cos(RADIANS(Customer.Longitude))*cos(".$lng."))-sin(RADIANS(Customer.Latitude))* sin(".$lat."))))","Distanz");

    $sqlQuery->addLeftJoin('Member','"Customer"."ID" = "Member"."ID"');
    $sqlQuery->addWhere("Member.ID IN(".implode(",", $userid).")");
    $sqlQuery->addLeftJoin('Customerproduct', '"Customer"."ID" = "Customerproduct"."CustomerID"');
    $sqlQuery->addLeftJoin('Customerinfo','"Customer"."ID" = "Customerinfo"."HofinfophotoID"');

    $sqlQuery->addWhere("(6367.41*SQRT(2*(1-cos(RADIANS(Customer.Latitude))*cos(".$lat.")*(sin(RADIANS(Customer.Longitude))*sin(".$lng.")+cos(RADIANS(Customer.Longitude))*cos(".$lng."))-sin(RADIANS(Customer.Latitude))*sin(".$lat."))) <= '".$distance."')");

    $sqlQuery->setOrderBy('Distanz','ASC');

    // Get the raw SQL (optional)
    $rawSQL = $sqlQuery->sql();

    // Execute and return a Query object
    $result = $sqlQuery->execute();

    $item = new ArrayList();

    // Iterate over results
    foreach($result as $row) {

    $output =new ArrayData(array(
    'Hof' => $row
    ));
    $item->push($output);
    }

    $sqlQuery = new SQLQuery();
    $sqlQuery->addFrom('Customerproduct_Productphoto');
    $sqlQuery->addLeftJoin('Customerproduct','"Customerproduct_Productphoto"."CustomerproductID" = "Customerproduct"."ID"');
    $sqlQuery->addWhere("Customerproduct.CustomerID IN(".implode(",", $userid).")");

    $rawSQL = $sqlQuery->sql();

    // Execute and return a Query object
    $result = $sqlQuery->execute();

    $fotoid = array();
    // Iterate over results
    foreach($result as $row) {

    $fotoid[$row['ImageID']] = $row['ImageID'];

    };

    $sqlQuery = new SQLQuery();
    $sqlQuery->addFrom('File');

    $sqlQuery->addWhere("ID IN(".implode(",", $fotoid).")");

    $rawSQL = $sqlQuery->sql();

    // Execute and return a Query object
    $result = $sqlQuery->execute();

    $itembilder = new ArrayList();

    // Iterate over results
    foreach($result as $row) {

    $output =new ArrayData(array(
    'Productbilder' => $row
    ));
    $itembilder->push($output);

    };

    $sqlQuery = new SQLQuery();
    $sqlQuery->addFrom('File');
    $sqlQuery->addLeftJoin('Customerinfo','"File"."ID" = "Customerinfo"."HofinfophotoID"');
    $sqlQuery->addWhere("Customerinfo.CustomerID IN(".implode(",", $userid).")");

    $rawSQL = $sqlQuery->sql();

    // Execute and return a Query object
    $result = $sqlQuery->execute();

    // Iterate over results
    foreach($result as $row) {

    $output =new ArrayData(array(
    'Hofbild' => $row
    ));
    $itembilder->push($output);

    };

    $hofdaten = array();
    $hofdaten['Hofdaten'] = $item;
    $bilder = array();
    $bilder['Bilder'] = $itembilder;

    $custom = array_merge($hofdaten,$bilder);

    $count = count($item);

    // Debug::show($custom);

    if($count > 0) {
    $this->setMessage('Success', 'Es konnten '.$count.' Treffer gefunden werden');

    } else {
    $this->setMessage('Error', 'Es konnten keine Treffer gefunden werden');
    }

    return $this->customise($custom);

    }

    my template looks like this, but the loop of Bilder does not work...:

    <% if Hofdaten %>
    <table class="searchtable">
    <tr><th>Hofname</th><th>Adresse</th></tr>

    <% loop Hofdaten %>

    <tr height="5" bgcolor="#e9e9e9"><td colspan="2"></td></tr>
    <tr bgcolor="#e9e9e9">

    <td><a data-toggle="modal" href="#suchresultat$Pos" class="">-> $Hof.Hofname</a>

    </td>
    <td>
    $Hof.Surname $Hof.FirstName<br />
    $Hof.Street<br />
    $Hof.PLZ $Hof.Place
    </td>
    </tr>
    <tr height="1" bgcolor="#ccc"><td colspan="2"></td></tr>
    <tr height="5" bgcolor="#e9e9e9"><td colspan="2">
    <!-- Modal -->
    <div class="modal hide fade" id="suchresultat$Pos">
    <div class="modal-dialog">
    <div class="modal-content">
    <div class="modal-header">
    <button type="button" class="close" data-dismiss="modal" aria-hidden="true">&times;</button>
    <img src="$ThemeDir/images/logo_hofladen_klein.png" alt="Hofladen" /><br /><br />

    </div>
    <div class="modal-body">

    <h4>$Hof.Hofname</h4>

    <% loop Bilder %>
    <img src="$Hofbild.Filename" />
    <% end_loop %>

    <p>$Hof.Surname $Hof.FirstName</p>
    <p>$Hof.PLZ $Hof.Street</p>
    <p>Telefon: $Hof.Phone</p>
    <p>Email: $Hof.Email</p>
    <p>Internet: <a href="$Hof.Homepage">$Hof.Homepage</a></p>
    <br />
    <br />
    <h5>Aus unserem Sortiment</h5>
    <p>
    $Hof.Productcategory, $Hof.Productsubcategory, $Hof.Product, $Hof.Price / $Hof.Einheit<br />
    $Hof.Description
    <img src="$Productbilder.Filename" />
    </p>

    </div>

    </div><!-- /.modal-content -->
    </div><!-- /.modal-dialog -->
    </div><!-- /.modal -->

    </td></tr>
    <% end_loop %>

    </table>

    <% end_if %>

  • Drumstick
    Avatar
    Community Member
    20 Posts

    Re: Arraylist and queries Link to this post

    Hi Willr

    I think I just see the solution to loop in the table the name of the Hof and the adress and then a link added with the user id. Then go to a detail page and show the result by query the database with the userid. Otherwise I don't understand how to bring together all this things like it is now.

  • Willr
    Avatar
    Forum Moderator
    5489 Posts

    Re: Arraylist and queries Link to this post

    The loop of Bilder doesn't work as it's not added to your returned DataObjectSet. If you want to loop Bilder within the loop of Hofdaten then the Bilder data has to be on each Hofdaten object.

    E.g based on your template you want to be passing the data structure something like

    function getHofdaten() {
    // ...
    foreach($result as $row) {
    //
    $bilder = new DataObjectSet();
    $bilder->push( .. );

    $output->push(new ArrayData(array(
    'Hof' => new ArrayData($hof),
    'Bilder => $bilder
    Productbilder' => something
    }

    return $output;
    }

    Remember when you use <% loop ObjName %> you are now inside the scope of ObjName and can only access properties in ObjName (or us $Up to go outside the loop) so depending on your data model you'll want to put everything into the correct scope.

    783 Views
Page: 1
Go to Top

Want to know more about the company that brought you SilverStripe? Then check out SilverStripe.com

Comments on this website? Please give feedback.