Combining Tables with Editor

bakamoo

Hi, im trying to see if this is possible. I may have 1 or 2 tables referencing to 1 table.
ie. Barcode table has references to Customers,Products,Users Table.

I want to display Barcode.customer_id as Barcode.customer_id + Customers.company
Then when a user edits it, a dropdown(for few items) or ajax search of available (customers) will be available. Of course the customer_id should be saved.

Could someone point out to me which tutorial i should follow? Thanks!

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


# Dump of table barcode
# ------------------------------------------------------------

DROP TABLE IF EXISTS `barcode`;

CREATE TABLE `barcode` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) DEFAULT NULL,
  `code` varchar(50) DEFAULT NULL,
  `product_id` int(20) DEFAULT NULL,
  `color` varchar(100) DEFAULT NULL,
  `created_by` int(20) DEFAULT NULL,
  `created_on` datetime DEFAULT NULL,
  `updated_by` int(20) DEFAULT NULL,
  `last_update` datetime DEFAULT NULL,
  `deleted` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

LOCK TABLES `barcode` WRITE;
/*!40000 ALTER TABLE `barcode` DISABLE KEYS */;

INSERT INTO `barcode` (`id`, `customer_id`, `code`, `product_id`, `color`, `created_by`, `created_on`, `updated_by`, `last_update`, `deleted`)
VALUES
    (1,32,'60319',47,'qq.09',9,'2018-05-09 00:00:00',9,'2018-05-09 00:00:00',0),
    (2,32,'60321',47,'qq.12',9,'2018-05-09 00:00:00',9,'2018-05-09 00:00:00',0),
    (3,32,'60322',47,'qq.18',9,'2018-05-09 00:00:00',9,'2018-05-09 00:00:00',0),
    (4,32,'60327',47,'qq.25',9,'2018-05-09 00:00:00',9,'2018-05-09 00:00:00',0),
    (5,32,'60329',47,'qq.27',9,'2018-05-09 00:00:00',9,'2018-05-09 00:00:00',0),
    (6,32,'60334',47,'qq.36',9,'2018-05-09 00:00:00',9,'2018-05-09 00:00:00',0),
    (7,32,'60341',47,'qq.59',9,'2018-05-09 00:00:00',9,'2018-05-09 00:00:00',0),
    (8,32,'60346',45,'qq.02190623',9,'2018-05-09 00:00:00',9,'2018-05-09 00:00:00',0),
    (9,32,'60360',48,'qq.06',9,'2018-05-09 00:00:00',9,'2018-05-09 00:00:00’,0);

/*!40000 ALTER TABLE `barcode` ENABLE KEYS */;
UNLOCK TABLES;


# Dump of table Customers
# ------------------------------------------------------------

DROP TABLE IF EXISTS `Customers`;

CREATE TABLE `Customers` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` int(20) DEFAULT NULL,
  `company` varchar(50) DEFAULT NULL,
  `agent` text,
  `terms` text,
  `delivery` varchar(200) DEFAULT NULL,
  `delivery_notes` text,
  `notes` text,
  `mgnt_notes` text,
  `created_by` int(20) DEFAULT NULL,
  `created_on` datetime DEFAULT NULL,
  `updated_by` int(20) DEFAULT NULL,
  `last_update` datetime DEFAULT NULL,
  `deleted` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

LOCK TABLES `Customers` WRITE;
/*!40000 ALTER TABLE `Customers` DISABLE KEYS */;

INSERT INTO `Customers` (`id`, `customer_id`, `company`, `agent`, `terms`, `delivery`, `delivery_notes`, `notes`, `mgnt_notes`, `created_by`, `created_on`, `updated_by`, `last_update`, `deleted`)
VALUES
    (1,17001,'Prince','','60 days',NULL,NULL,NULL,NULL,9,'2017-07-28 00:00:00',9,'2017-07-28 00:00:00',0),
    (2,17002,'Paper & More','','',NULL,NULL,NULL,NULL,9,'2017-07-28 00:00:00',9,'2017-07-28 00:00:00',0),
    (3,17003,'Party In The City','','',NULL,NULL,NULL,NULL,9,'2017-07-28 00:00:00',9,'2017-07-28 00:00:00',0),
    (4,17004,'Buttons & Wrap','','',NULL,NULL,NULL,NULL,9,'2017-07-28 00:00:00',9,'2017-07-28 00:00:00',1),
    (5,17005,'SM','','',NULL,NULL,NULL,NULL,9,'2017-07-28 00:00:00',9,'2017-07-28 00:00:00',1),
    (6,17006,'Landmark','','',NULL,NULL,NULL,NULL,9,'2017-07-28 00:00:00',9,'2017-07-28 00:00:00',1),
    (7,17007,'Buttons & Wrap','','',NULL,NULL,NULL,NULL,9,'2017-07-28 00:00:00',9,'2017-07-28 00:00:00',0),
    (8,17008,'All Toys / All Shoppe','','',NULL,NULL,NULL,NULL,9,'2017-07-28 00:00:00',9,'2017-07-28 00:00:00',0),
    (9,17009,'MPH','','',NULL,NULL,NULL,NULL,9,'2017-07-28 00:00:00',9,'2017-07-28 00:00:00',0),
    (10,17010,'Lazada','','',NULL,NULL,NULL,NULL,9,'2017-07-28 00:00:00',9,'2017-07-28 00:00:00’,0);

/*!40000 ALTER TABLE `Customers` ENABLE KEYS */;
UNLOCK TABLES;



/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

bakamoo

I noticed this

->leftJoin( 'sites', 'sites.id', '=', 'users.site' )

from https://editor.datatables.net/examples/simple/join.html

I may have a problem with this as there was a time i had like 5-6 left joins and doing it in mysql is extremely slower compared to getting everything and having the server do the processing

allan

Hi,

Have you got indexes on all of the columns you are doing joins on? If not, add them, that will seriously help performance.

Regarding your original question, you are correct - using a join is going to be the way to go here. To show multiple pieces of information in a single cell, use a renderer - see this example where the first column is a combination of the first name and last name.

Allan