Joining tables.

menashe

Hi Allan,

My previous question (about the SQL) stemmed from the following.

I have Table A, with a foreign key to Table B, which has a list of values (a lookup table). I have no issue displaying all of the values in Table B in a combo box (using select or selectize) and updating.

If I changed it so that table B now has (only) two foreign keys--one to Table C (with values for manufacturers) and one to Table D (with values for brands), I still can display the values, but when I update (select another value), it is not reflected in the Datatable.

Is this doable?

allan

Can you show me the PHP code you are using for that? Is it writing changes to Table B for the foreign keys?

Can you also add ->debug(true) before ->process(...) (if you don't already have it) and show me the response from the server for an edit request?

Thanks,
Allan

menashe

$items = Editor::inst($db, 'items')
  ->idPrefix('item_')
  ->fields(
    Field::inst('items.id')->set(false)
      ->options(
        Options::inst()
          ->table('items')
          ->value('id')
          ->label('item')
      )
      ->setFormatter(Format::ifEmpty(null)),
    Field::inst('items.item')
      ->validator(Validate::notEmpty(ValidateOptions::inst()
        ->message('An Item name is required'))),
    Field::inst('items.prefix')
      ->setFormatter(Format::ifEmpty(null)),
    Field::inst('items.suffix')
      ->setFormatter(Format::ifEmpty(null)),
    Field::inst('items.company_id'),
      // ->options(
      //   Options::inst()
      //     ->table('companies')
      //     ->value('id')
      //     ->label('manufacturer_id')
      //     ->label('brand_id')
      // )
      // ->setFormatter(Format::ifEmpty(null)),
    Field::inst('companies.manufacturer_id'),
    Field::inst('manufacturers.manufacturer')
      ->options(
        Options::inst()
          ->table('manufacturers')
          ->value('id')
          ->label('manufacturer')
      )
      ->setFormatter(Format::ifEmpty(null)),
    Field::inst('companies.brand_id'),
    Field::inst('brands.brand')
      ->options(
        Options::inst()
          ->table('brands')
          ->value('id')
          ->label('brand')
      )
      ->setFormatter(Format::ifEmpty(null)),
    Field::inst('items.category_id')
      ->options(
        Options::inst()
          ->table('categories')
          ->value('id')
          ->label('category')
      )
      ->validator(Validate::dbValues(null, 'id', 'categories', $db))
      ->validator(Validate::notEmpty(ValidateOptions::inst()
        ->message('A Category is required'))),
    Field::inst('categories.category'),
    Field::inst('categories.sub_category1'),
    Field::inst('categories.sub_category2'),
    Field::inst('items.web'),
    Field::inst('items.notes')
      ->setFormatter(Format::ifEmpty(null)),
    Field::inst('ih2.parent')
      ->getFormatter(function ($val, $data) {
        return $val ? 1 : null;
      }),
    // Field::inst('items.do_not_repurchase')
    //   ->getFormatter(function ($val, $data) {
    //     return !$val ? 0 : 1;
    //   })
    //   ->setFormatter(function ($val, $data, $opts) {
    //     return !$val ? null : 1;
    //   }),
    Field::inst('search.search_fields'),
    Field::inst('hierarchy.count')
      ->getFormatter(function ($val, $data) {
        return !$val ? 0 : $val;
      }),
    Field::inst('price_range.item_id'),
    Field::inst('price_range.lowest_price'),
    Field::inst('price_range.outer_packages'),
    Field::inst('price_range.inner_packages'),
    Field::inst('price_range.inner_items'),
    Field::inst('price_range.unit_quantity'),
    Field::inst('price_range.is_net_weight'),
    Field::inst('price_range.unit'),
  )
  ->leftJoin('items_hierarchy', 'items_hierarchy.child', '=', 'items.id')
  ->leftJoin('(SELECT DISTINCT parent FROM items_hierarchy) AS ih2', 'ih2.parent', '=', 'items.id')
  ->leftJoin('companies', 'companies.id', '=', 'items.company_id')
  ->leftJoin('manufacturers', 'manufacturers.id', '=', 'companies.manufacturer_id')
  ->leftJoin('brands', 'brands.id', '=', 'companies.brand_id')
  ->leftJoin('categories', 'categories.id', '=', 'items.category_id')
  ->leftJoin('(SELECT item_id, MIN(lowest_price) AS lowest_price, MIN(outer_packages) AS outer_packages, MIN(inner_packages) AS inner_packages, MIN(inner_items) AS inner_items, MIN(unit_quantity) AS unit_quantity, MIN(is_net_weight) AS is_net_weight, MIN(unit) AS unit FROM price_range GROUP BY item_id) AS price_range', 'price_range.item_id = items.id')
  ->leftJoin('(SELECT items_hierarchy.parent, count(items_hierarchy.child) count FROM items_hierarchy WHERE items_hierarchy.child IS NOT NULL GROUP BY items_hierarchy.parent) AS hierarchy', 'hierarchy.parent', '=', 'items.id')
  ->leftJoin('(SELECT i.id, STRING_AGG (coalesce(pkg.unit_quantity, 0) || \' \' || \' \' || coalesce(pkg.detail, \' \') || \' \' || coalesce(pkg.model_number, \' \') || \' \' || coalesce(pkg.upc_ean, \' \') || \' \' || coalesce(pkg.itm_art, \' \') || \' \' || coalesce(pkg.sku), \' \') AS search_fields FROM items i 
              LEFT JOIN packaging pkg on pkg.item_id = i.id 
              GROUP BY i.id) AS search', 'search.id', '=', 'items.id')

  // Exclude records that are children of other records.
  ->where('items_hierarchy.child', null, '=')
  ->debug(true)
  ->process($_POST)
  ->data();

header('Content-Type: application/json; charset=utf-8');
echo json_encode($items);

menashe

kthorngren

Allan asked for the response from the edit request that introduces the error. What you posted above looks like a the response to a draw() request.

Kevin

menashe

There is no "error"--just not working as I expect.

The Datatable looks fine.

In the Editor, the Manufacturer and Brand do not display.

I think that rather than the following ids being returned from the server for companies

I want the actual manufacturer and brand names, but I cannot figure that out.

rf1234

I have Table A, with a foreign key to Table B, which has a list of values (a lookup table). I have no issue displaying all of the values in Table B in a combo box (using select or selectize) and updating.

Sorry, I don't understand what "and updating" means in this context.

But let me try to understand this.

I see you have an "items" table. One row in that table is the item "Internal Hard Drive". This table has one direct link (via foreign key, no link table!) to the "companies" table. The foreign key in "items" is "items.company_id". "Companies" itself has direct links (also foreign keys) to "manufacturers" and "brands". The foreign keys in "companies" are "companies.manufacturer_id" and "companies.brand_id".

The database design means:
- One "item" can be assigned to a "company". It cannot be assigned to a "manufacturer" or a "brand" directly.
- This implicitly means: It doesn't make a lot of sense to have dropdowns to select "manufacturer" and "brand" for an "item". Instead you need a dropdown for "company".
- If you select a "company" for an "item" you have implicitly selected "brand" AND "manufacturer" because they come with "company"!

This is the code that is "telling" me the above:

$items = Editor::inst($db, 'items')->idPrefix('item_')
....
->leftJoin('companies', 'companies.id', '=', 'items.company_id')
->leftJoin('manufacturers', 'manufacturers.id', '=', 'companies.manufacturer_id')
->leftJoin('brands', 'brands.id', '=', 'companies.brand_id')

The code below cannot work because working on table "items" you simply cannot select a "brand" or a "manufacturer". All you can select is a "company" that implicitly contains the selection of "brand" and "manufacturer" because "company" contains both foreign keys. In addition it doesn't make much sense to update fields in "manufacturers" and "brands": The foreign keys are in table "companies"; you only need the options from tables "manufacturers" and "brands", but you don't update those tables.

    Field::inst('companies.manufacturer_id'),
    Field::inst('manufacturers.manufacturer')
      ->options(
        Options::inst()
          ->table('manufacturers')
          ->value('id')
          ->label('manufacturer')
      )
      ->setFormatter(Format::ifEmpty(null)),
    Field::inst('companies.brand_id'),
    Field::inst('brands.brand')
      ->options(
        Options::inst()
          ->table('brands')
          ->value('id')
          ->label('brand')
      )

You would need something like this if you really wanted to make this work (but you probably won't want this because it is not the "business outcome" you want to achieve, I guess.)

    Field::inst('items.company_id')
      ->options(
        Options::inst()
          ->table('companies')
          ->value('id')
          ->label('name')
      )

Depending on what you are really trying to achieve you may have to redesign your application OR your database. Good luck!

Roland

menashe

Good morning Roland,

Very perceptive. This is a change that I made in the last 24 or so hours. Manufacturer and brand were in the items table, as well as web--and everything worked.

Then it occurred to me that an item doesn't have a web address--and brand does! (One manufacturer may have many brands, each with a different web address.)

So... this was an attempt to rectify this.

It has also occurred to me--almost from the beginning--that my new design is wrong, and hence the issue.

Your comments above are **much appreciated! **