We launched new forums in March 2019—join us there. In a hurry for help with your website? Get Help Now!
MODX Community Forums Archive

Answered MIGXdb: CMP: How to display value from other table in column

  • labr1005 Reply #1, 11 years, 5 months ago
    Here's my problem:
    I have two tables ("clients" and "works") with an composite/aggregate-relationship (one client, many works).
    I choose a "client" for a new "work" in a listbox with an @SELECT-binding ("@SELECT `title` AS `name`, `id` FROM `modx_clients`").

    So I have the client's ID but I want to display the client's name in my CMP grid for "works" and not the ID. I need some sort of binding for the MIGXdb CMP columns.

    I hope you get what I want. smiley Check the attachment, too.

    Any ideas and/or hints?

    Thanks,
    Lars

    By the way: MIGXdb really is what I was waiting for! Really great, thanks a lot for this!

    This question has been answered by Bruno17. See the first response.

      • 4172
      • 5,888 Posts
      Bruno17 Reply #2, 11 years, 5 months ago
      can you show your xml-schema?
        -------------------------------

        you can buy me a beer, if you like MIGX

        http://webcmsolutions.de/migx.html

        Thanks!
      • labr1005 Reply #3, 11 years, 5 months ago
        Sure.

        <?xml version="1.0" encoding="UTF-8"?>
<model package="clientWork" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" version="1.1">
	<object class="Client" table="clients" extends="xPDOSimpleObject">
		<field key="title" dbtype="varchar" precision="255" phptype="string" null="false" default="" />
		<field key="logo" dbtype="varchar" precision="255" phptype="string" null="false" default="" />
		<field key="createdon" dbtype="datetime" phptype="datetime" null="true" />
		<field key="createdby" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
		<field key="editedon" dbtype="datetime" phptype="datetime" null="true" />
		<field key="editedby" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
		<field key="deleted" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="0" />
		<field key="published" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="0" />
		<composite alias="Work" class="Work" local="id" foreign="clientId" cardinality="many" owner="local" />
		<aggregate alias="CreatedBy" class="modUser" local="createdby" foreign="id" cardinality="one" owner="foreign" />
		<aggregate alias="EditedBy" class="modUser" local="editedby" foreign="id" cardinality="one" owner="foreign" />
	</object>
	<object class="Work" table="works" extends="xPDOSimpleObject">
		<field key="clientId" dbtype="int" precision="10" phptype="integer" null="false" index="index" />
		<field key="title" dbtype="varchar" precision="255" phptype="string" null="false" default="" />
		<field key="description" dbtype="text" phptype="string" null="false" default="" />
		<field key="createdon" dbtype="datetime" phptype="datetime" null="true" />
		<field key="createdby" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
		<field key="editedon" dbtype="datetime" phptype="datetime" null="true" />
		<field key="editedby" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
		<field key="deleted" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="0" />
		<field key="published" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="0" />
		<aggregate alias="Client" class="Client" local="clientId" foreign="id" cardinality="one" owner="foreign" />
		<aggregate alias="CreatedBy" class="modUser" local="createdby" foreign="id" cardinality="one" owner="foreign" />
		<aggregate alias="EditedBy" class="modUser" local="editedby" foreign="id" cardinality="one" owner="foreign" />
	</object>
</model>
        • discuss.answer
          • 4172
          • 5,888 Posts
          Bruno17 Reply #4, 11 years, 5 months ago

          ok, you can get the client-title by adding this line to the joins-field in the MIGX-Configurator:

          [{"alias":"Client"}]


          this should add all fields of the client-table

          use as fieldname in your column: Client_title

          if you want to limit the fields, you can do it for example by: 
          [{"alias":"Client","selectfields":"title,logo"}]
            -------------------------------

            you can buy me a beer, if you like MIGX

            http://webcmsolutions.de/migx.html

            Thanks!
          • labr1005 Reply #5, 11 years, 5 months ago
            Perfect, thank you.
              • 34109
              • 119 Posts
              [email protected] Reply #6, 11 years, 2 months ago
              Just wondering, is there a way of joining more than one table at a time?
                Studio Republic
                http://www.studiorepublic.com
                0845 226 3205
                @christodhunter
                • 4172
                • 5,888 Posts
                Bruno17 Reply #7, 11 years, 2 months ago 
                [{"alias":"Client","selectfields":"title,logo"},{"alias":"AnotherAlias"},{"alias":"notInSchema","classname":"modResource","on":"modResource.id=myClass.resource_id"}]
                [ed. note: Bruno17 last edited this post 11 years, 2 months ago.]
                  -------------------------------

                  you can buy me a beer, if you like MIGX

                  http://webcmsolutions.de/migx.html

                  Thanks!
                  • 34109
                  • 119 Posts
                  [email protected] Reply #8, 11 years, 2 months ago
                  Thanks for your help Bruno, and thanks for MIGX - it's made a huge difference to how we approach MODX projects
                    Studio Republic
                    http://www.studiorepublic.com
                    0845 226 3205
                    @christodhunter