答:
Use NSNumber like this:

```<code>int yourInt = 5;
[myMutableArray addObject:[NSNumber numberWithInt:yourInt]];
</code>```

Or using modern Objective C syntax, you can use the following for expressions:

```<code>[myMutableArray addObject:@(2 + 3)];
</code>```

Or for lone numbers:

```<code>[myMutableArray addObject:@5];
</code>```

Title: How do you store "int" values in an NSMutableArray* or NSMutableDictionary*? Chronic problems with JSON data that come in as integers.

Body:
How do you store "int" values in an NSMutableArray or NSMutableDictionary?  Chronic problems with JSON data that come in as integers.

Should I try to store these integers as NSNumber objects or just as strings that contain the integer?

How dangerous is it to do a "raw" (int) conversion on the pointers if I know the values will always be Natural Numbers (numbers>=0 including zero for null.)

The integers I'm always working with are Foreign Key Id's from a database.

Body:
Use NSNumber like this:

int yourInt = 5;
[myMutableArray addObject:[NSNumber numberWithInt:yourInt]];

Or using modern Objective C syntax, you can use the following for expressions:

[myMutableArray addObject:@(2 + 3)];

Or for lone numbers:

[myMutableArray addObject:@5];


来源: stackexchange

问:
Querying Joined Tables
My application is a loans processing platform

For this question, let us assume there are 5 tables. Below is a brief description of what these tables do, and the current number of records.

loans - holds the loans data - 1.5m records

clients - holds client name etc - 500k records

client_coms - client phone and email records - 1.5m records

client_addresses - client address records - 550k records

clients_banks - client bank account data - 520k records

I have added create table queries and some test data at the bottom of the question.

My problem is geting a query to run efficiently when searching for values held in joined tables.

Hypothetical Scenario:

I have a client with ID 12516

There are 4 records in the client _coms table related to this client, one of which has the value tony@findme.com

I need to search for a record in the loans table which has a client with the email address of tony@findme.com

I am currently joining the clients_coms table to the loans table ON client_id, and then grouping by client_id

This is taking quite a while to run especially f there are multiple criteria in the where clause

Example query:

```<code>SELECT l.*, c.*
FROM loans l
LEFT JOIN clients c ON l.client_id = c.client_id
LEFT JOIN clients_coms com ON l.client_id = com.client_id
LEFT JOIN clients_addresses ad ON l.client_id = ad.client_id
LEFT JOIN clients_emp emp ON l.client_id = emp.client_id
LEFT JOIN clients_banks bank ON l.client_id = bank.client_id
WHERE com.value = 'llyamjamie@aol.com'
AND bank.sort_code = '309374'
GROUP BY l.loan_id
</code>```

Is there any way to do this more efficiantly, and preferably without the GROUP BY loan_id

Create Table Queries and Test Data

```    SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
    SET time_zone = "+00:00";

    CREATE TABLE `clients` (
      `client_id` int(11) NOT NULL,
      `username` varchar(150) NOT NULL,
      `password` varchar(32) NOT NULL,
      `clientref` varchar(20) NOT NULL,
      `title_id` int(11) DEFAULT NULL,
      `fname` varchar(100) DEFAULT NULL,
      `mname` varchar(100) DEFAULT NULL,
      `lname` varchar(100) DEFAULT NULL,
      `dob` date DEFAULT NULL,
      `dependants` int(3) DEFAULT NULL,
      `marital_id` int(11) DEFAULT NULL,
      `status_difficulty_id` int(11) NOT NULL DEFAULT '1',
      `blacklisted` tinyint(1) NOT NULL DEFAULT '0',
      `market_consent` tinyint(1) NOT NULL DEFAULT '0',
      `market_broker` tinyint(1) NOT NULL DEFAULT '0',
      `created` timestamp NULL DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    INSERT INTO `clients` (`client_id`, `username`, `password`, `clientref`, `title_id`, `fname`, `mname`, `lname`, `dob`, `dependants`, `marital_id`, `status_difficulty_id`, `blacklisted`, `market_consent`, `market_broker`, `created`) VALUES
    (12516, 'tony@strat55yinternet.com', 'e8fec09af5441b940b200a0df09039db', 'Yamantaka108!', 1, 'Anthony', NULL, 'Simmons', '1961-11-26', 1, 1, 1, 0, 1, 1, '2017-02-27 01:08:26'),
    (12517, 'trythis@ungdfgdfg.co.uk', '0cef1fb10f60529028a71f58e54ed07b', 'Password1!', 1, 'testme', NULL, 'testmeagain', '1983-09-19', 0, 3, 1, 0, 1, 0, '2017-04-21 08:59:16'),
    (12518, 'teddy.highlander@stradfgdfnternet.com', 'e8fec09af5441b940b200a0df09039db', 'Yamantaka108!', 1, 'Teddy', NULL, 'Highlander', '1990-01-01', 2, 2, 1, 0, 1, 0, '2017-04-21 09:37:08'),
    (12519, 'terry.v@stradfgyinternet.com', 'e8fec09af5441b940b200a0df09039db', 'Yamantaka108!', 1, 'Terry', NULL, 'Vanerslat', '1990-01-01', 2, 2, 1, 0, 1, 1, '2017-04-21 09:42:53'),
    (12520, 'testybanker@stratedfgets.com', 'e8fec09af5441b940b200a0df09039db', 'Yamantaka108!', 1, 'Testy', NULL, 'Banker', '1990-01-01', 1, 1, 1, 0, 0, 1, '2017-04-21 19:17:29'),
    (12521, 'teddy.kruger@stradfgrnet.com', 'e8fec09af5441b940b200a0df09039db', 'Yamantaka108!', 1, 'teddy', NULL, 'kruger', '1995-07-12', 1, 1, 1, 0, 1, 1, '2017-05-01 08:23:04'),
    (12522, 'test123@test.com', '0cef1fb10f60529028a71f58e54ed07b', 'Password1!', 1, 'wptest', NULL, 'wptest', '1990-01-01', 0, 3, 1, 0, 0, 0, '2017-05-02 16:51:06'),
    (12523, 'test123@undfggk.co.uk', '0cef1fb10f60529028a71f58e54ed07b', 'Password1!', 1, 'wptest', NULL, 'wptest', '1980-01-01', 0, 2, 1, 0, 0, 1, '2017-05-08 09:46:24'),
    (12524, '43201at78285n316xl@udgfdfgdf.co.uk', '16125873728ecaa5663548acd8708dc1', 'Digitalis1!', 1, 'wptester', NULL, 'wptester', '1980-01-01', 0, 3, 1, 0, 1, 1, '2017-05-08 10:09:48'),
    (12525, 'test@test.com', '7b91953d6f7ace4bd58f5e27b92129b2', 'M4ll1ng$', 2, 'test', NULL, 'test', '1988-03-15', 1, 1, 1, 0, 1, 0, '2017-05-12 10:20:18'),
    (12526, 'terry@terry.com', 'e8fec09af5441b940b200a0df09039db', 'Yamantaka108!', 1, 'Terry', NULL, 'Vanguard', '1998-02-03', 2, 2, 1, 0, 1, 0, '2017-05-12 21:51:34'),
    (12527, 'tony@tester.com', 'f44de07db359724e8be77b679d648261', 'C&e3zCyl', 2, 'Tony', NULL, 'Tester', '1998-06-16', 1, 1, 1, 0, 0, 0, '2017-05-12 22:01:57'),
    (12528, 'terry@tester.com', 'e8fec09af5441b940b200a0df09039db', 'Yamantaka108!', 2, 'Terry', NULL, 'Tester', '1998-10-20', 1, 2, 1, 0, 0, 1, '2017-05-12 22:08:22'),
    (12529, 'info@myemail.com', 'e8fec09af5441b940b200a0df09039db', 'Yamantaka108!', 1, 'Anthony', NULL, 'Simmons', '1980-02-05', 2, 2, 1, 0, 0, 1, '2017-05-12 23:14:28'),
    (12530, 'sgmudfgrray@lidfgve.co.uk', '8889c4ff30a036e69af92c00e7381f9b', 'Trepalle$666', 1, 'Steve', NULL, 'Murray', '1966-02-07', 2, 2, 1, 0, 0, 0, '2017-05-16 10:05:04'),
    (12533, 'mbucgper@undfgck.co.uk', '7b91953d6f7ace4bd58f5e27b92129b2', 'M4ll1ng$', 1, 'Matt', NULL, 'Smith', '1970-01-27', 0, 1, 1, 0, 0, 0, '2017-05-17 10:55:23'),
    (12534, '1@unerteruck.co.uk', '7b91953d6f7ace4bd58f5e27b92129b2', 'M4ll1ng$', 1, 'Jacob', NULL, 'Matthews', '1960-05-31', 0, 1, 1, 0, 0, 0, '2017-05-17 15:17:56'),
    (12535, '2@uncrteterk.co.uk', '7b91953d6f7ace4bd58f5e27b92129b2', 'M4ll1ng$', 4, 'Jess', NULL, 'Brown', '1960-06-09', 0, 2, 1, 0, 0, 0, '2017-05-17 15:45:02'),
    (12536, 'test@straerttester.com', 'e8fec09af5441b940b200a0df09039db', 'Yamantaka108!', 1, 'Test', NULL, 'Testerly', '1997-09-22', 1, 1, 1, 0, 1, 0, '2017-05-24 05:13:46'),
    (12539, 'test@test34324.com', '5db95e2186901ec55d959bc0db190bc8', 'RfsBMZ_w', 1, 'Matt', NULL, 'smith', '1960-02-02', 4, 1, 1, 0, 0, 0, '2017-09-25 07:33:57'),
    (12540, 'test@gmail.com', 'a42316cf2aedfa853893628eb74bcfb4', 'PF8r!W&R', 4, 'Ema', NULL, 'Gupta', '1920-02-03', 1, 1, 1, 0, 1, 0, '2017-10-09 11:20:00'),
    (12542, 'rgupta@unertetck.com', 'f925916e2754e5e03f75dd58a5733251', 'Test@123', 3, 'Ruchi  ', NULL, 'Gupta', '1983-01-01', 0, 1, 1, 0, 1, 0, '2017-10-09 13:09:49');

    CREATE TABLE `clients_addresses` (
      `client_address_id` int(11) NOT NULL,
      `client_id` int(11) NOT NULL,
      `house_number` varchar(20) DEFAULT NULL,
      `house_name` varchar(50) DEFAULT NULL,
      `street` varchar(100) DEFAULT NULL,
      `city` varchar(50) DEFAULT NULL,
      `county` varchar(70) DEFAULT NULL,
      `postcode` varchar(10) DEFAULT NULL,
      `home_status_id` int(11) DEFAULT NULL,
      `movein_date` date DEFAULT NULL,
      `current` tinyint(1) NOT NULL DEFAULT '1',
      `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    INSERT INTO `clients_addresses` (`client_address_id`, `client_id`, `house_number`, `house_name`, `street`, `city`, `county`, `postcode`, `home_status_id`, `movein_date`, `current`, `created`) VALUES
    (590, 12516, '1', 'Paddock House', 'Merrow Street', 'Guildford', NULL, 'GU47AG', 1, '2000-02-01', 1, '2017-02-27 01:09:17'),
    (591, 12519, '45', 'willow tree', 'smith street', 'southfields', NULL, 'Sw185dj', 1, '2000-01-01', 1, '2017-04-21 09:43:44'),
    (592, 12520, '44', 'Patcick house', 'pastel St', 'Guildford', NULL, 'Gu47ag', 1, '1998-01-01', 1, '2017-04-21 19:18:16'),
    (593, 12521, '1', 'Haybarn House', 'Guildford Rd', 'Guildford', NULL, 'GU12QL', 1, '1998-01-01', 1, '2017-05-01 08:23:43'),
    (594, 12522, '221b Baker Street', '', 'Baker Street', 'bromley', NULL, 'NW1 6XE', 1, '2005-12-01', 1, '2017-05-02 16:52:46'),
    (595, 12523, '5-6', '', 'Matthews Street', 'Rugby', NULL, 'CV21 3BY', 1, '2001-01-01', 1, '2017-05-08 09:47:58'),
    (596, 12524, '21', '', 'Henry Street', 'Stratford', NULL, 'CV37 6QW', 1, '2000-12-01', 1, '2017-05-08 10:11:16'),
    (597, 12525, '707', '', 'Churchill Way', 'Westerham', NULL, 'TN16 3BN', 1, '2008-04-01', 1, '2017-05-12 10:21:00'),
    (598, 12528, '58', '', 'High Path Road', 'Guildford', NULL, 'GU1 2QL', 1, '2017-01-01', 1, '2017-05-12 22:11:46'),
    (599, 12528, '', '2 Paddock House', 'Merrow Street', 'Guildford', NULL, 'GU4 7AG', 2, '2016-01-01', 0, '2017-05-12 22:11:46'),
    (600, 12529, '3', 'Britannia House', 'Caerphilly Business Park', 'Caerphilly', NULL, 'CF83 3GG', 2, '2012-02-01', 1, '2017-05-12 23:14:46'),
    (601, 12530, '1', 'Cronks Farm', 'Hampstead Lane', 'Maidstone', NULL, 'ME185HN', 1, '2004-04-01', 1, '2017-05-16 10:06:27'),
    (604, 12542, '707', '', 'Biggnin Hill', 'westerham', NULL, 'TN16 3BN', 1, '2011-01-01', 1, '2017-10-09 13:10:42');

    CREATE TABLE `clients_banks` (
      `client_bank_id` int(11) NOT NULL,
      `client_id` int(11) NOT NULL,
      `bank_id` int(11) DEFAULT NULL,
      `sort_code` varchar(6) DEFAULT NULL,
      `account` varchar(8) DEFAULT NULL,
      `bank_name` varchar(70) DEFAULT NULL COMMENT 'Returned by AFD API',
      `active` tinyint(1) NOT NULL DEFAULT '1',
      `created` timestamp NULL DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    INSERT INTO `clients_banks` (`client_bank_id`, `client_id`, `bank_id`, `sort_code`, `account`, `bank_name`, `active`, `created`) VALUES
    (544, 12516, 3, '309374', '03522844', 'Lloyds Bank PLC', 0, '2017-02-27 01:11:47'),
    (551, 12516, 3, '301641', '60722260', 'Lloyds International', 1, '2017-02-28 06:16:41'),
    (552, 12516, 3, '309384', '03544844', 'Lloyds Bank PLC', 0, '2017-02-28 06:17:57'),
    (553, 12519, 3, '804378', '03555844', 'Lloyds Bank PLC', 1, '2017-04-21 09:52:12'),
    (554, 12520, 3, '309355', '03555844', 'Lloyds Bank PLC', 1, '2017-04-21 19:21:09'),
    (555, 12521, 3, '802374', '03566844', 'Lloyds Bank PLC', 1, '2017-05-01 08:37:04'),
    (556, 12523, 3, '602044', '00000000', 'Nat West Bank PLC', 1, '2017-05-08 09:54:26'),
    (557, 12524, 3, '902046', '00000000', 'Nat West Bank PLC', 1, '2017-05-08 10:15:03'),
    (558, 12525, 5, '602044', '00000000', 'Nat West Bank PLC', 1, '2017-05-12 11:42:42'),
    (559, 12528, 3, '309674', '03577844', 'Lloyds Bank PLC', 1, '2017-05-12 22:15:12'),
    (560, 12529, 3, '399379', '03588844', 'Lloyds Bank PLC', 0, '2017-05-12 23:18:40'),
    (561, 12529, 3, '909374', '03511844', 'Lloyds Bank PLC', 1, '2017-05-12 23:25:53');

    CREATE TABLE `clients_coms` (
      `client_coms_id` int(11) NOT NULL,
      `client_id` int(11) NOT NULL,
      `coms_id` int(11) NOT NULL,
      `value` varchar(150) NOT NULL,
      `active` tinyint(1) NOT NULL DEFAULT '1',
      `main` tinyint(1) NOT NULL DEFAULT '1',
      `user_id` int(11) NOT NULL,
      `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    INSERT INTO `clients_coms` (`client_coms_id`, `client_id`, `coms_id`, `value`, `active`, `main`, `user_id`, `created`) VALUES
    (1685, 12516, 1, 'tony@findme.com', 0, 0, 0, '2017-02-27 01:08:26'),
    (1685, 12516, 1, 'tony@findmeagain.com', 0, 0, 0, '2017-02-27 01:08:26'),
    (1686, 12516, 2, '07081447123', 1, 1, 0, '2017-02-27 01:08:26'),
    (1687, 12516, 3, '02081447423', 1, 1, 0, '2017-02-27 01:08:26'),
    (1688, 12516, 4, '02081447128', 1, 1, 0, '2017-02-27 01:10:08'),
    (1689, 12517, 1, 'trythis@sdfsdfsdf.co.uk', 0, 1, 0, '2017-04-21 08:59:16'),
    (1690, 12517, 2, '07702419620', 0, 1, 0, '2017-04-21 08:59:16'),
    (1691, 12517, 3, '07702019420', 0, 1, 0, '2017-04-21 08:59:16'),
    (1692, 12518, 1, 'teddy.highlander@sdfsdfsdf.com', 1, 1, 0, '2017-04-21 09:37:08'),
    (1693, 12518, 2, '07232344566', 1, 1, 0, '2017-04-21 09:37:08'),
    (1694, 12518, 3, '02928848888', 1, 1, 0, '2017-04-21 09:37:08'),
    (1695, 12519, 1, 'terry.v@sdfsdfsd.com', 1, 1, 0, '2017-04-21 09:42:53'),
    (1696, 12519, 2, '07332242323', 1, 1, 0, '2017-04-21 09:42:53'),
    (1697, 12519, 3, '02048787656', 1, 1, 0, '2017-04-21 09:42:53'),
    (1698, 12519, 4, '02089448776', 1, 1, 0, '2017-04-21 09:46:45'),
    (1699, 12520, 1, 'testybanker@sdfsd.com', 1, 1, 0, '2017-04-21 19:17:29'),
    (1700, 12520, 2, '07224232345', 1, 1, 0, '2017-04-21 19:17:29'),
    (1701, 12520, 3, '02083438383', 1, 1, 0, '2017-04-21 19:17:29'),
    (1702, 12520, 4, '02082384282', 1, 1, 0, '2017-04-21 19:19:00'),
    (1703, 12516, 1, 'tday@sdfsdfsdfs.co.uk', 1, 1, 18, '2017-04-24 08:57:18'),
    (1704, 12521, 1, 'teddy.kruger@sdfsdfsd.com', 1, 1, 0, '2017-05-01 08:23:04'),
    (1705, 12521, 2, '07665865665', 1, 1, 0, '2017-05-01 08:23:04'),
    (1706, 12521, 3, '02087278888', 1, 1, 0, '2017-05-01 08:23:04'),
    (1707, 12521, 4, '02098828722', 1, 1, 0, '2017-05-01 08:24:26'),
    (1708, 12522, 1, 'test123@test.com', 1, 1, 0, '2017-05-02 16:51:06'),
    (1709, 12522, 2, '07507254489', 1, 1, 0, '2017-05-02 16:51:06'),
    (1710, 12522, 3, '07207254489', 1, 1, 0, '2017-05-02 16:51:06'),
    (1711, 12522, 4, '01522904210', 1, 1, 0, '2017-05-02 16:55:13'),
    (1712, 12523, 1, 'test123@fdgdfg.co.uk', 1, 1, 0, '2017-05-08 09:46:24'),
    (1713, 12523, 2, '07494254592', 1, 1, 0, '2017-05-08 09:46:24'),
    (1714, 12523, 3, '07494254592', 1, 1, 0, '2017-05-08 09:46:24'),
    (1715, 12523, 4, '01980512110', 1, 1, 0, '2017-05-08 09:49:29'),
    (1716, 12524, 1, '43201at78285n316xl@undgdfck.co.uk', 1, 1, 0, '2017-05-08 10:09:48'),
    (1717, 12524, 2, '07494854590', 1, 1, 0, '2017-05-08 10:09:48'),
    (1718, 12524, 3, '01420414510', 1, 1, 0, '2017-05-08 10:09:48'),
    (1719, 12524, 4, '07494854592', 1, 1, 0, '2017-05-08 10:13:02'),
    (1720, 12525, 1, 'test@test.com', 1, 1, 0, '2017-05-12 10:20:18'),
    (1721, 12525, 2, '07343242342', 1, 1, 0, '2017-05-12 10:20:18'),
    (1722, 12525, 3, '01731123456', 1, 1, 0, '2017-05-12 10:20:18'),
    (1723, 12525, 4, '01734123456', 1, 1, 0, '2017-05-12 10:21:49'),
    (1724, 12526, 1, 'terry@terry.com', 1, 1, 0, '2017-05-12 21:51:34'),
    (1725, 12526, 2, '07123333333', 1, 1, 0, '2017-05-12 21:51:34'),
    (1726, 12526, 3, '03034334343', 1, 1, 0, '2017-05-12 21:51:34'),
    (1727, 12527, 1, 'tony@tester.com', 1, 0, 0, '2017-05-12 22:01:57'),
    (1728, 12527, 2, '07544444444', 1, 1, 0, '2017-05-12 22:01:57'),
    (1729, 12527, 3, '02086767676', 1, 1, 0, '2017-05-12 22:01:57'),
    (1730, 12528, 1, 'terry@tester.com', 1, 1, 0, '2017-05-12 22:08:22'),
    (1731, 12528, 2, '07332232222', 1, 1, 0, '2017-05-12 22:08:22'),
    (1732, 12528, 3, '02098888888', 1, 1, 0, '2017-05-12 22:08:22'),
    (1733, 12528, 4, '02089992929', 1, 1, 0, '2017-05-12 22:13:02'),
    (1734, 12529, 1, 'info@myemail.com', 1, 1, 0, '2017-05-12 23:14:28'),
    (1735, 12529, 2, '07086667656', 1, 1, 0, '2017-05-12 23:14:28'),
    (1736, 12529, 3, '02098887878', 1, 1, 0, '2017-05-12 23:14:28'),
    (1737, 12529, 4, '02087776767', 1, 1, 0, '2017-05-12 23:15:17'),
    (1738, 12530, 1, 'sgmudfgrray@live.co.uk', 1, 1, 0, '2017-05-16 10:05:04'),
    (1739, 12530, 2, '07771755011', 1, 1, 0, '2017-05-16 10:05:04'),
    (1740, 12530, 3, '01622817656', 1, 1, 0, '2017-05-16 10:05:04'),
    (1741, 12530, 4, '02037475718', 1, 1, 0, '2017-05-16 10:07:43'),
    (1748, 12533, 1, 'mbudfgper@undfgdk.co.uk', 1, 1, 0, '2017-05-17 10:55:23'),
    (1749, 12533, 2, '07000123456', 1, 1, 0, '2017-05-17 10:55:23'),
    (1750, 12533, 3, '02030000000', 1, 1, 0, '2017-05-17 10:55:23'),
    (1751, 12534, 1, '1@uncdfgdfgck.co.uk', 1, 1, 0, '2017-05-17 15:17:56'),
    (1752, 12534, 2, '07000000000', 1, 1, 0, '2017-05-17 15:17:56'),
    (1753, 12534, 3, '02000000000', 1, 1, 0, '2017-05-17 15:17:56'),
    (1754, 12535, 1, '2@uncldfgdfk.co.uk', 1, 1, 0, '2017-05-17 15:45:02'),
    (1755, 12535, 2, '07000000001', 1, 1, 0, '2017-05-17 15:45:02'),
    (1756, 12535, 3, '02030000001', 1, 1, 0, '2017-05-17 15:45:02'),
    (1757, 12536, 1, 'test@stra890tedfgster.com', 1, 1, 0, '2017-05-24 05:13:46'),
    (1758, 12536, 2, '07332232277', 1, 1, 0, '2017-05-24 05:13:46'),
    (1759, 12536, 3, '02998879888', 1, 1, 0, '2017-05-24 05:13:46'),
    (1760, 12539, 1, 'test@test34324.com', 1, 1, 0, '2017-09-25 07:33:58'),
    (1763, 12540, 1, 'test@gmail.com', 1, 1, 0, '2017-10-09 11:20:00'),
    (1764, 12540, 2, '07123456789', 1, 1, 0, '2017-10-09 11:20:00'),
    (1765, 12540, 3, '01123456789', 1, 1, 0, '2017-10-09 11:20:00'),
    (1766, 12540, 4, '01234567895', 1, 1, 0, '2017-10-09 11:31:23'),
    (1770, 12540, 1, 'gargruchi12@gmail.com', 0, 0, 30, '2017-10-09 13:07:24'),
    (1771, 12542, 1, 'rgupta@uddfgdfgk.com', 1, 1, 0, '2017-10-09 13:09:49'),
    (1772, 12542, 2, '07507245489', 1, 1, 0, '2017-10-09 13:09:49'),
    (1773, 12542, 3, '01282560110', 1, 1, 0, '2017-10-09 13:09:49'),
    (1774, 12542, 4, '01282560110', 1, 1, 0, '2017-10-09 13:11:48'),
    (1775, 12542, 4, '01324564564', 1, 1, 0, '2017-10-09 13:14:34'),
    (1776, 12527, 1, 'rob@nedgfdfgdls.co.uk', 1, 1, 31, '2017-10-12 09:42:13');

    CREATE TABLE `loans` (
      `loan_id` int(11) NOT NULL,
      `client_id` int(11) NOT NULL,
      `product_id` int(11) NOT NULL DEFAULT '1',
      `amount` decimal(7,2) NOT NULL DEFAULT '0.00',
      `interest_percent` decimal(7,2) DEFAULT '0.00' COMMENT 'Daily Interest Rate',
      `instalments` int(3) DEFAULT NULL,
      `fraud_id` int(11) NOT NULL DEFAULT '1',
      `code` varchar(4) NOT NULL,
      `code_sent` tinyint(1) NOT NULL DEFAULT '0',
      `signed` tinyint(1) DEFAULT '0',
      `signed_date` date DEFAULT NULL,
      `approved_amount` decimal(7,2) NOT NULL DEFAULT '0.00',
      `approved_user_id` int(11) DEFAULT NULL,
      `rtd` tinyint(1) NOT NULL DEFAULT '0',
      `rtd_date` datetime DEFAULT NULL,
      `deposited_amount` decimal(7,2) NOT NULL DEFAULT '0.00',
      `deposited_date` date DEFAULT NULL,
      `depsosited_user_id` int(11) DEFAULT NULL,
      `due_date` date DEFAULT NULL,
      `assigned_user_id` int(11) DEFAULT '0',
      `content_message_id` int(11) DEFAULT NULL,
      `balance` decimal(7,2) DEFAULT NULL,
      `payout` decimal(7,2) DEFAULT NULL,
      `repaid` tinyint(1) NOT NULL DEFAULT '0',
      `repaid_date` date DEFAULT NULL,
      `writeoff` tinyint(1) NOT NULL DEFAULT '0',
      `writeoff_date` date DEFAULT NULL,
      `writeoff_user_id` int(11) DEFAULT NULL,
      `apr` decimal(10,2) DEFAULT NULL,
      `sweep` tinyint(1) NOT NULL DEFAULT '1',
      `test` tinyint(1) NOT NULL DEFAULT '0',
      `ip` varchar(20) DEFAULT NULL,
      `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    INSERT INTO `loans` (`loan_id`, `client_id`, `product_id`, `amount`, `interest_percent`, `instalments`, `fraud_id`, `code`, `code_sent`, `signed`, `signed_date`, `approved_amount`, `approved_user_id`, `rtd`, `rtd_date`, `deposited_amount`, `deposited_date`, `depsosited_user_id`, `due_date`, `assigned_user_id`, `content_message_id`, `balance`, `payout`, `repaid`, `repaid_date`, `writeoff`, `writeoff_date`, `writeoff_user_id`, `apr`, `sweep`, `test`, `ip`, `created`) VALUES
    (9549, 12516, 4, '200.00', '0.80', 6, 1, '7812', 0, 1, '2017-02-27', '200.00', 1, 0, NULL, '200.00', '2017-01-03', 1, NULL, 1, 4, '90.22', '90.22', 0, NULL, 0, NULL, NULL, '1266.04', 1, 0, NULL, '2017-02-27 01:08:26'),
    (9550, 12517, 4, '100.00', '0.80', 6, 1, '7521', 0, 0, NULL, '0.00', NULL, 0, NULL, '0.00', NULL, NULL, NULL, 18, NULL, '0.00', NULL, 0, NULL, 0, NULL, NULL, NULL, 1, 0, NULL, '2017-04-21 08:59:16'),
    (9551, 12518, 4, '100.00', '0.80', 6, 1, '7146', 0, 0, NULL, '0.00', NULL, 0, NULL, '0.00', NULL, NULL, NULL, 0, NULL, NULL, NULL, 0, NULL, 0, NULL, NULL, NULL, 1, 0, NULL, '2017-04-21 09:37:08'),
    (9552, 12519, 4, '100.00', '0.80', 6, 1, '9524', 0, 0, NULL, '0.00', 0, 0, NULL, '0.00', NULL, NULL, NULL, 0, 2, NULL, NULL, 0, NULL, 0, NULL, NULL, NULL, 1, 0, NULL, '2017-04-21 09:42:53'),
    (9553, 12520, 1, '100.00', '0.80', 3, 1, '2597', 0, 0, NULL, '0.00', 0, 0, NULL, '0.00', NULL, NULL, NULL, 0, 2, NULL, NULL, 0, NULL, 0, NULL, NULL, NULL, 1, 0, NULL, '2017-04-21 19:17:29'),
    (9554, 12521, 1, '100.00', '0.80', 3, 1, '8057', 0, 0, NULL, '0.00', 0, 0, NULL, '0.00', NULL, NULL, NULL, 0, 2, NULL, NULL, 0, NULL, 0, NULL, NULL, NULL, 1, 0, NULL, '2017-05-01 08:23:04'),
    (9555, 12516, 4, '200.00', '0.80', 6, 1, '7812', 0, 1, '2017-03-27', '200.00', 1, 0, NULL, '200.00', '2017-02-03', 1, NULL, 1, 4, '508.80', NULL, 0, NULL, 0, NULL, NULL, '1266.04', 1, 0, NULL, '2017-03-27 00:08:26'),
    (9556, 12522, 1, '100.00', '0.80', 3, 1, '2976', 0, 0, NULL, '0.00', NULL, 0, NULL, '0.00', NULL, NULL, NULL, 0, NULL, NULL, NULL, 0, NULL, 0, NULL, NULL, NULL, 1, 0, NULL, '2017-05-02 16:51:06'),
    (9557, 12523, 1, '100.00', '0.80', 3, 1, '4366', 0, 0, NULL, '0.00', 0, 0, NULL, '0.00', NULL, NULL, NULL, 0, 2, NULL, NULL, 0, NULL, 0, NULL, NULL, NULL, 1, 0, NULL, '2017-05-08 09:46:24'),
    (9558, 12524, 1, '100.00', '0.80', 3, 1, '9040', 0, 0, NULL, '100.00', 18, 0, NULL, '0.00', NULL, NULL, NULL, 0, 2, NULL, NULL, 0, NULL, 0, NULL, NULL, NULL, 1, 0, NULL, '2017-05-08 10:09:48'),
    (9559, 12525, 1, '250.00', '0.80', 3, 1, '4416', 0, 0, NULL, '0.00', 0, 0, NULL, '0.00', NULL, NULL, NULL, 0, 2, NULL, NULL, 0, NULL, 0, NULL, NULL, NULL, 1, 0, NULL, '2017-05-12 10:20:18'),
    (9560, 12526, 1, '175.00', '0.80', 3, 1, '2242', 0, 0, NULL, '0.00', NULL, 0, NULL, '0.00', NULL, NULL, NULL, 0, NULL, NULL, NULL, 0, NULL, 0, NULL, NULL, NULL, 1, 0, NULL, '2017-05-12 21:51:34'),
    (9561, 12527, 1, '100.00', '0.80', 3, 1, '2623', 0, 0, NULL, '0.00', NULL, 0, NULL, '0.00', NULL, NULL, NULL, 0, NULL, NULL, NULL, 0, NULL, 0, NULL, NULL, NULL, 1, 0, NULL, '2017-05-12 22:01:57'),
    (9562, 12528, 1, '100.00', '0.80', 3, 1, '4932', 1, 1, '2017-05-12', '100.00', 1, 1, '2017-05-13 05:14:06', '0.00', NULL, NULL, NULL, 0, 2, NULL, NULL, 0, NULL, 0, NULL, NULL, NULL, 1, 0, NULL, '2017-05-12 22:08:22'),
    (9563, 12529, 1, '150.00', '0.80', 3, 1, '8136', 0, 0, NULL, '0.00', 0, 0, NULL, '0.00', NULL, NULL, NULL, 0, 2, NULL, NULL, 0, NULL, 0, NULL, NULL, NULL, 1, 0, NULL, '2017-05-12 23:14:28'),
    (9564, 12530, 1, '350.00', '0.80', 3, 1, '2248', 0, 0, NULL, '0.00', 0, 0, NULL, '0.00', NULL, NULL, NULL, 0, 2, NULL, NULL, 0, NULL, 0, NULL, NULL, NULL, 1, 0, NULL, '2017-05-16 10:05:04'),
    (9565, 12536, 1, '200.00', '0.80', 3, 1, '8079', 0, 0, NULL, '0.00', NULL, 0, NULL, '0.00', NULL, NULL, NULL, 0, NULL, NULL, NULL, 0, NULL, 0, NULL, NULL, NULL, 1, 0, NULL, '2017-05-24 05:13:46'),
    (9567, 12539, 1, '300.00', '0.80', 3, 1, '8223', 0, 0, NULL, '0.00', NULL, 0, NULL, '0.00', NULL, NULL, NULL, 0, NULL, NULL, NULL, 0, NULL, 0, NULL, NULL, NULL, 1, 0, NULL, '2017-09-25 07:33:58'),
    (9568, 12540, 1, '375.00', '0.80', 3, 1, '9123', 0, 0, NULL, '0.00', 0, 0, NULL, '0.00', NULL, NULL, NULL, 0, 2, NULL, NULL, 0, NULL, 0, NULL, NULL, NULL, 1, 0, NULL, '2017-10-09 11:20:00'),
    (9570, 12542, 1, '100.00', '0.80', 3, 1, '4470', 0, 0, NULL, '0.00', NULL, 0, NULL, '0.00', NULL, NULL, NULL, 0, NULL, NULL, NULL, 0, NULL, 0, NULL, NULL, NULL, 1, 0, NULL, '2017-10-09 13:09:49');

    ALTER TABLE `clients`
      ADD PRIMARY KEY (`client_id`),
      ADD KEY `fname` (`fname`),
      ADD KEY `created` (`created`),
      ADD KEY `lname` (`lname`),
      ADD KEY `title_id` (`title_id`),
      ADD KEY `username` (`username`),
      ADD KEY `clientref` (`clientref`),
      ADD KEY `dob` (`dob`),
      ADD KEY `market_consent` (`market_consent`),
      ADD KEY `blacklisted` (`blacklisted`),
      ADD KEY `market_broker` (`market_broker`),
      ADD KEY `marital_id` (`marital_id`),
      ADD KEY `status_difficulty_id` (`status_difficulty_id`);

    ALTER TABLE `clients_addresses`
      ADD PRIMARY KEY (`client_address_id`),
      ADD KEY `created` (`created`),
      ADD KEY `postcode` (`postcode`),
      ADD KEY `client_id` (`client_id`),
      ADD KEY `move_in_date` (`movein_date`),
      ADD KEY `current` (`current`),
      ADD KEY `home_status_id` (`home_status_id`);

    ALTER TABLE `clients_banks`
      ADD PRIMARY KEY (`client_bank_id`),
      ADD KEY `created` (`created`),
      ADD KEY `sort_code` (`sort_code`),
      ADD KEY `bank_id` (`bank_id`),
      ADD KEY `client_id` (`client_id`),
      ADD KEY `active` (`active`);

    ALTER TABLE `clients_coms`
      ADD PRIMARY KEY (`client_coms_id`),
      ADD KEY `client_id` (`client_id`),
      ADD KEY `value` (`value`),
      ADD KEY `active` (`active`),
      ADD KEY `created` (`created`),
      ADD KEY `coms_id` (`coms_id`),
      ADD KEY `primary_2` (`main`),
      ADD KEY `user_id` (`user_id`);

    ALTER TABLE `loans`
      ADD PRIMARY KEY (`loan_id`),
      ADD KEY `client_id` (`client_id`),
      ADD KEY `product_id` (`product_id`),
      ADD KEY `depsoited_user_id` (`depsosited_user_id`),
      ADD KEY `deposited_date` (`deposited_date`),
      ADD KEY `approved_user_id` (`approved_user_id`),
      ADD KEY `deposited_amount` (`deposited_amount`),
      ADD KEY `created` (`created`),
      ADD KEY `repaid` (`repaid`),
      ADD KEY `repaid_date` (`repaid_date`),
      ADD KEY `sweep` (`sweep`),
      ADD KEY `due_date` (`due_date`),
      ADD KEY `ip` (`ip`),
      ADD KEY `signed` (`signed`),
      ADD KEY `signed_date` (`signed_date`),
      ADD KEY `fraud_id` (`fraud_id`),
      ADD KEY `assigned_user_id` (`assigned_user_id`),
      ADD KEY `code_sent` (`code_sent`),
      ADD KEY `test` (`test`),
      ADD KEY `content_message_id` (`content_message_id`),
      ADD KEY `rtd` (`rtd`),
      ADD KEY `rtd_date` (`rtd_date`),
      ADD KEY `rtd_2` (`rtd`,`deposited_amount`),
      ADD KEY `writeoff` (`writeoff`),
      ADD KEY `writeoff_date` (`writeoff_date`),
      ADD KEY `payout` (`payout`),
      ADD KEY `balance` (`balance`),
      ADD KEY `writeoff_user_id` (`writeoff_user_id`);

    ALTER TABLE `clients`
      MODIFY `client_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12543;

    ALTER TABLE `clients_addresses`
      MODIFY `client_address_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=605;

    ALTER TABLE `clients_banks`
      MODIFY `client_bank_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=564;

    ALTER TABLE `clients_coms`
      MODIFY `client_coms_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1777;

    ALTER TABLE `loans`
      MODIFY `loan_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9571;

    ALTER TABLE `clients_addresses`
      ADD CONSTRAINT `clients_addresses_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`client_id`) ON DELETE CASCADE ON UPDATE CASCADE,

    ALTER TABLE `clients_banks`
      ADD CONSTRAINT `clients_banks_ibfk_2` FOREIGN KEY (`client_id`) REFERENCES `clients` (`client_id`) ON DELETE CASCADE ON UPDATE CASCADE;

    ALTER TABLE `clients_coms`
      ADD CONSTRAINT `clients_coms_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`client_id`) ON DELETE CASCADE ON UPDATE CASCADE,

    ALTER TABLE `loans`
      ADD CONSTRAINT `loans_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`client_id`) ON DELETE CASCADE ON UPDATE CASCADE,
```

答:
Your query looks like selecting all table fields:

Inefficient:

It include all the records before it display the fields you wanted.

It making your query become too slow when the times comes you got a millions of data.

Performance problem.

To Resolve:

Just select specific fields that you need like:

Avoid using Select *

```<code>select a.f1,a.f2,b.f3,a.f4,b.f5 from
(select f1,f2,f4 from table1) as a
left join
(select f1, f3,f5 from table2) as b
on a.f1 = b.f1
</code>```

as for me this is to be called table query optimizing

Off course All indexes of your tables are done and created.

ContentLicense: CC BY-SA 3.0

Title: Querying Joined Tables

Body:
My application is a loans processing platform

For this question, let us assume there are 5 tables. Below is a brief description of what these tables do, and the current number of records.

loans - holds the loans data - 1.5m records

clients - holds client name etc - 500k records

client_coms - client phone and email records - 1.5m records

client_addresses - client address records - 550k records

clients_banks - client bank account data - 520k records

I have added create table queries and some test data at the bottom of the question.

My problem is geting a query to run efficiently when searching for values held in joined tables.

Hypothetical Scenario:

I have a client with ID 12516

There are 4 records in the client _coms table related to this client, one of which has the value tony@findme.com

I need to search for a record in the loans table which has a client with the email address of tony@findme.com

I am currently joining the clients_coms table to the loans table ON client_id, and then grouping by client_id

This is taking quite a while to run especially f there are multiple criteria in the where clause

Example query:

SELECT l.*, c.*
FROM loans l
LEFT JOIN clients c ON l.client_id = c.client_id
LEFT JOIN clients_coms com ON l.client_id = com.client_id
LEFT JOIN clients_addresses ad ON l.client_id = ad.client_id
LEFT JOIN clients_emp emp ON l.client_id = emp.client_id
LEFT JOIN clients_banks bank ON l.client_id = bank.client_id
WHERE com.value = 'llyamjamie@aol.com'
AND bank.sort_code = '309374'
GROUP BY l.loan_id

Is there any way to do this more efficiantly, and preferably without the GROUP BY loan_id

Create Table Queries and Test Data

    SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
    SET time_zone = "+00:00";

    CREATE TABLE `clients` (
      `client_id` int(11) NOT NULL,
      `username` varchar(150) NOT NULL,
      `password` varchar(32) NOT NULL,
      `clientref` varchar(20) NOT NULL,
      `title_id` int(11) DEFAULT NULL,
      `fname` varchar(100) DEFAULT NULL,
      `mname` varchar(100) DEFAULT NULL,
      `lname` varchar(100) DEFAULT NULL,
      `dob` date DEFAULT NULL,
      `dependants` int(3) DEFAULT NULL,
      `marital_id` int(11) DEFAULT NULL,
      `status_difficulty_id` int(11) NOT NULL DEFAULT '1',
      `blacklisted` tinyint(1) NOT NULL DEFAULT '0',
      `market_consent` tinyint(1) NOT NULL DEFAULT '0',
      `market_broker` tinyint(1) NOT NULL DEFAULT '0',
      `created` timestamp NULL DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    INSERT INTO `clients` (`client_id`, `username`, `password`, `clientref`, `title_id`, `fname`, `mname`, `lname`, `dob`, `dependants`, `marital_id`, `status_difficulty_id`, `blacklisted`, `market_consent`, `market_broker`, `created`) VALUES
    (12516, 'tony@strat55yinternet.com', 'e8fec09af5441b940b200a0df09039db', 'Yamantaka108!', 1, 'Anthony', NULL, 'Simmons', '1961-11-26', 1, 1, 1, 0, 1, 1, '2017-02-27 01:08:26'),
    (12517, 'trythis@ungdfgdfg.co.uk', '0cef1fb10f60529028a71f58e54ed07b', 'Password1!', 1, 'testme', NULL, 'testmeagain', '1983-09-19', 0, 3, 1, 0, 1, 0, '2017-04-21 08:59:16'),
    (12518, 'teddy.highlander@stradfgdfnternet.com', 'e8fec09af5441b940b200a0df09039db', 'Yamantaka108!', 1, 'Teddy', NULL, 'Highlander', '1990-01-01', 2, 2, 1, 0, 1, 0, '2017-04-21 09:37:08'),
    (12519, 'terry.v@stradfgyinternet.com', 'e8fec09af5441b940b200a0df09039db', 'Yamantaka108!', 1, 'Terry', NULL, 'Vanerslat', '1990-01-01', 2, 2, 1, 0, 1, 1, '2017-04-21 09:42:53'),
    (12520, 'testybanker@stratedfgets.com', 'e8fec09af5441b940b200a0df09039db', 'Yamantaka108!', 1, 'Testy', NULL, 'Banker', '1990-01-01', 1, 1, 1, 0, 0, 1, '2017-04-21 19:17:29'),
    (12521, 'teddy.kruger@stradfgrnet.com', 'e8fec09af5441b940b200a0df09039db', 'Yamantaka108!', 1, 'teddy', NULL, 'kruger', '1995-07-12', 1, 1, 1, 0, 1, 1, '2017-05-01 08:23:04'),
    (12522, 'test123@test.com', '0cef1fb10f60529028a71f58e54ed07b', 'Password1!', 1, 'wptest', NULL, 'wptest', '1990-01-01', 0, 3, 1, 0, 0, 0, '2017-05-02 16:51:06'),
    (12523, 'test123@undfggk.co.uk', '0cef1fb10f60529028a71f58e54ed07b', 'Password1!', 1, 'wptest', NULL, 'wptest', '1980-01-01', 0, 2, 1, 0, 0, 1, '2017-05-08 09:46:24'),
    (12524, '43201at78285n316xl@udgfdfgdf.co.uk', '16125873728ecaa5663548acd8708dc1', 'Digitalis1!', 1, 'wptester', NULL, 'wptester', '1980-01-01', 0, 3, 1, 0, 1, 1, '2017-05-08 10:09:48'),
    (12525, 'test@test.com', '7b91953d6f7ace4bd58f5e27b92129b2', 'M4ll1ng$', 2, 'test', NULL, 'test', '1988-03-15', 1, 1, 1, 0, 1, 0, '2017-05-12 10:20:18'),
    (12526, 'terry@terry.com', 'e8fec09af5441b940b200a0df09039db', 'Yamantaka108!', 1, 'Terry', NULL, 'Vanguard', '1998-02-03', 2, 2, 1, 0, 1, 0, '2017-05-12 21:51:34'),
    (12527, 'tony@tester.com', 'f44de07db359724e8be77b679d648261', 'C&e3zCyl', 2, 'Tony', NULL, 'Tester', '1998-06-16', 1, 1, 1, 0, 0, 0, '2017-05-12 22:01:57'),
    (12528, 'terry@tester.com', 'e8fec09af5441b940b200a0df09039db', 'Yamantaka108!', 2, 'Terry', NULL, 'Tester', '1998-10-20', 1, 2, 1, 0, 0, 1, '2017-05-12 22:08:22'),
    (12529, 'info@myemail.com', 'e8fec09af5441b940b200a0df09039db', 'Yamantaka108!', 1, 'Anthony', NULL, 'Simmons', '1980-02-05', 2, 2, 1, 0, 0, 1, '2017-05-12 23:14:28'),
    (12530, 'sgmudfgrray@lidfgve.co.uk', '8889c4ff30a036e69af92c00e7381f9b', 'Trepalle$666', 1, 'Steve', NULL, 'Murray', '1966-02-07', 2, 2, 1, 0, 0, 0, '2017-05-16 10:05:04'),
    (12533, 'mbucgper@undfgck.co.uk', '7b91953d6f7ace4bd58f5e27b92129b2', 'M4ll1ng$', 1, 'Matt', NULL, 'Smith', '1970-01-27', 0, 1, 1, 0, 0, 0, '2017-05-17 10:55:23'),
    (12534, '1@unerteruck.co.uk', '7b91953d6f7ace4bd58f5e27b92129b2', 'M4ll1ng$', 1, 'Jacob', NULL, 'Matthews', '1960-05-31', 0, 1, 1, 0, 0, 0, '2017-05-17 15:17:56'),
    (12535, '2@uncrteterk.co.uk', '7b91953d6f7ace4bd58f5e27b92129b2', 'M4ll1ng$', 4, 'Jess', NULL, 'Brown', '1960-06-09', 0, 2, 1, 0, 0, 0, '2017-05-17 15:45:02'),
    (12536, 'test@straerttester.com', 'e8fec09af5441b940b200a0df09039db', 'Yamantaka108!', 1, 'Test', NULL, 'Testerly', '1997-09-22', 1, 1, 1, 0, 1, 0, '2017-05-24 05:13:46'),
    (12539, 'test@test34324.com', '5db95e2186901ec55d959bc0db190bc8', 'RfsBMZ_w', 1, 'Matt', NULL, 'smith', '1960-02-02', 4, 1, 1, 0, 0, 0, '2017-09-25 07:33:57'),
    (12540, 'test@gmail.com', 'a42316cf2aedfa853893628eb74bcfb4', 'PF8r!W&R', 4, 'Ema', NULL, 'Gupta', '1920-02-03', 1, 1, 1, 0, 1, 0, '2017-10-09 11:20:00'),
    (12542, 'rgupta@unertetck.com', 'f925916e2754e5e03f75dd58a5733251', 'Test@123', 3, 'Ruchi  ', NULL, 'Gupta', '1983-01-01', 0, 1, 1, 0, 1, 0, '2017-10-09 13:09:49');

    CREATE TABLE `clients_addresses` (
      `client_address_id` int(11) NOT NULL,
      `client_id` int(11) NOT NULL,
      `house_number` varchar(20) DEFAULT NULL,
      `house_name` varchar(50) DEFAULT NULL,
      `street` varchar(100) DEFAULT NULL,
      `city` varchar(50) DEFAULT NULL,
      `county` varchar(70) DEFAULT NULL,
      `postcode` varchar(10) DEFAULT NULL,
      `home_status_id` int(11) DEFAULT NULL,
      `movein_date` date DEFAULT NULL,
      `current` tinyint(1) NOT NULL DEFAULT '1',
      `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    INSERT INTO `clients_addresses` (`client_address_id`, `client_id`, `house_number`, `house_name`, `street`, `city`, `county`, `postcode`, `home_status_id`, `movein_date`, `current`, `created`) VALUES
    (590, 12516, '1', 'Paddock House', 'Merrow Street', 'Guildford', NULL, 'GU47AG', 1, '2000-02-01', 1, '2017-02-27 01:09:17'),
    (591, 12519, '45', 'willow tree', 'smith street', 'southfields', NULL, 'Sw185dj', 1, '2000-01-01', 1, '2017-04-21 09:43:44'),
    (592, 12520, '44', 'Patcick house', 'pastel St', 'Guildford', NULL, 'Gu47ag', 1, '1998-01-01', 1, '2017-04-21 19:18:16'),
    (593, 12521, '1', 'Haybarn House', 'Guildford Rd', 'Guildford', NULL, 'GU12QL', 1, '1998-01-01', 1, '2017-05-01 08:23:43'),
    (594, 12522, '221b Baker Street', '', 'Baker Street', 'bromley', NULL, 'NW1 6XE', 1, '2005-12-01', 1, '2017-05-02 16:52:46'),
    (595, 12523, '5-6', '', 'Matthews Street', 'Rugby', NULL, 'CV21 3BY', 1, '2001-01-01', 1, '2017-05-08 09:47:58'),
    (596, 12524, '21', '', 'Henry Street', 'Stratford', NULL, 'CV37 6QW', 1, '2000-12-01', 1, '2017-05-08 10:11:16'),
    (597, 12525, '707', '', 'Churchill Way', 'Westerham', NULL, 'TN16 3BN', 1, '2008-04-01', 1, '2017-05-12 10:21:00'),
    (598, 12528, '58', '', 'High Path Road', 'Guildford', NULL, 'GU1 2QL', 1, '2017-01-01', 1, '2017-05-12 22:11:46'),
    (599, 12528, '', '2 Paddock House', 'Merrow Street', 'Guildford', NULL, 'GU4 7AG', 2, '2016-01-01', 0, '2017-05-12 22:11:46'),
    (600, 12529, '3', 'Britannia House', 'Caerphilly Business Park', 'Caerphilly', NULL, 'CF83 3GG', 2, '2012-02-01', 1, '2017-05-12 23:14:46'),
    (601, 12530, '1', 'Cronks Farm', 'Hampstead Lane', 'Maidstone', NULL, 'ME185HN', 1, '2004-04-01', 1, '2017-05-16 10:06:27'),
    (604, 12542, '707', '', 'Biggnin Hill', 'westerham', NULL, 'TN16 3BN', 1, '2011-01-01', 1, '2017-10-09 13:10:42');

    CREATE TABLE `clients_banks` (
      `client_bank_id` int(11) NOT NULL,
      `client_id` int(11) NOT NULL,
      `bank_id` int(11) DEFAULT NULL,
      `sort_code` varchar(6) DEFAULT NULL,
      `account` varchar(8) DEFAULT NULL,
      `bank_name` varchar(70) DEFAULT NULL COMMENT 'Returned by AFD API',
      `active` tinyint(1) NOT NULL DEFAULT '1',
      `created` timestamp NULL DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    INSERT INTO `clients_banks` (`client_bank_id`, `client_id`, `bank_id`, `sort_code`, `account`, `bank_name`, `active`, `created`) VALUES
    (544, 12516, 3, '309374', '03522844', 'Lloyds Bank PLC', 0, '2017-02-27 01:11:47'),
    (551, 12516, 3, '301641', '60722260', 'Lloyds International', 1, '2017-02-28 06:16:41'),
    (552, 12516, 3, '309384', '03544844', 'Lloyds Bank PLC', 0, '2017-02-28 06:17:57'),
    (553, 12519, 3, '804378', '03555844', 'Lloyds Bank PLC', 1, '2017-04-21 09:52:12'),
    (554, 12520, 3, '309355', '03555844', 'Lloyds Bank PLC', 1, '2017-04-21 19:21:09'),
    (555, 12521, 3, '802374', '03566844', 'Lloyds Bank PLC', 1, '2017-05-01 08:37:04'),
    (556, 12523, 3, '602044', '00000000', 'Nat West Bank PLC', 1, '2017-05-08 09:54:26'),
    (557, 12524, 3, '902046', '00000000', 'Nat West Bank PLC', 1, '2017-05-08 10:15:03'),
    (558, 12525, 5, '602044', '00000000', 'Nat West Bank PLC', 1, '2017-05-12 11:42:42'),
    (559, 12528, 3, '309674', '03577844', 'Lloyds Bank PLC', 1, '2017-05-12 22:15:12'),
    (560, 12529, 3, '399379', '03588844', 'Lloyds Bank PLC', 0, '2017-05-12 23:18:40'),
    (561, 12529, 3, '909374', '03511844', 'Lloyds Bank PLC', 1, '2017-05-12 23:25:53');

    CREATE TABLE `clients_coms` (
      `client_coms_id` int(11) NOT NULL,
      `client_id` int(11) NOT NULL,
      `coms_id` int(11) NOT NULL,
      `value` varchar(150) NOT NULL,
      `active` tinyint(1) NOT NULL DEFAULT '1',
      `main` tinyint(1) NOT NULL DEFAULT '1',
      `user_id` int(11) NOT NULL,
      `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    INSERT INTO `clients_coms` (`client_coms_id`, `client_id`, `coms_id`, `value`, `active`, `main`, `user_id`, `created`) VALUES
    (1685, 12516, 1, 'tony@findme.com', 0, 0, 0, '2017-02-27 01:08:26'),
    (1685, 12516, 1, 'tony@findmeagain.com', 0, 0, 0, '2017-02-27 01:08:26'),
    (1686, 12516, 2, '07081447123', 1, 1, 0, '2017-02-27 01:08:26'),
    (1687, 12516, 3, '02081447423', 1, 1, 0, '2017-02-27 01:08:26'),
    (1688, 12516, 4, '02081447128', 1, 1, 0, '2017-02-27 01:10:08'),
    (1689, 12517, 1, 'trythis@sdfsdfsdf.co.uk', 0, 1, 0, '2017-04-21 08:59:16'),
    (1690, 12517, 2, '07702419620', 0, 1, 0, '2017-04-21 08:59:16'),
    (1691, 12517, 3, '07702019420', 0, 1, 0, '2017-04-21 08:59:16'),
    (1692, 12518, 1, 'teddy.highlander@sdfsdfsdf.com', 1, 1, 0, '2017-04-21 09:37:08'),
    (1693, 12518, 2, '07232344566', 1, 1, 0, '2017-04-21 09:37:08'),
    (1694, 12518, 3, '02928848888', 1, 1, 0, '2017-04-21 09:37:08'),
    (1695, 12519, 1, 'terry.v@sdfsdfsd.com', 1, 1, 0, '2017-04-21 09:42:53'),
    (1696, 12519, 2, '07332242323', 1, 1, 0, '2017-04-21 09:42:53'),
    (1697, 12519, 3, '02048787656', 1, 1, 0, '2017-04-21 09:42:53'),
    (1698, 12519, 4, '02089448776', 1, 1, 0, '2017-04-21 09:46:45'),
    (1699, 12520, 1, 'testybanker@sdfsd.com', 1, 1, 0, '2017-04-21 19:17:29'),
    (1700, 12520, 2, '07224232345', 1, 1, 0, '2017-04-21 19:17:29'),
    (1701, 12520, 3, '02083438383', 1, 1, 0, '2017-04-21 19:17:29'),
    (1702, 12520, 4, '02082384282', 1, 1, 0, '2017-04-21 19:19:00'),
    (1703, 12516, 1, 'tday@sdfsdfsdfs.co.uk', 1, 1, 18, '2017-04-24 08:57:18'),
    (1704, 12521, 1, 'teddy.kruger@sdfsdfsd.com', 1, 1, 0, '2017-05-01 08:23:04'),
    (1705, 12521, 2, '07665865665', 1, 1, 0, '2017-05-01 08:23:04'),
    (1706, 12521, 3, '02087278888', 1, 1, 0, '2017-05-01 08:23:04'),
    (1707, 12521, 4, '02098828722', 1, 1, 0, '2017-05-01 08:24:26'),
    (1708, 12522, 1, 'test123@test.com', 1, 1, 0, '2017-05-02 16:51:06'),
    (1709, 12522, 2, '07507254489', 1, 1, 0, '2017-05-02 16:51:06'),
    (1710, 12522, 3, '07207254489', 1, 1, 0, '2017-05-02 16:51:06'),
    (1711, 12522, 4, '01522904210', 1, 1, 0, '2017-05-02 16:55:13'),
    (1712, 12523, 1, 'test123@fdgdfg.co.uk', 1, 1, 0, '2017-05-08 09:46:24'),
    (1713, 12523, 2, '07494254592', 1, 1, 0, '2017-05-08 09:46:24'),
    (1714, 12523, 3, '07494254592', 1, 1, 0, '2017-05-08 09:46:24'),
    (1715, 12523, 4, '01980512110', 1, 1, 0, '2017-05-08 09:49:29'),
    (1716, 12524, 1, '43201at78285n316xl@undgdfck.co.uk', 1, 1, 0, '2017-05-08 10:09:48'),
    (1717, 12524, 2, '07494854590', 1, 1, 0, '2017-05-08 10:09:48'),
    (1718, 12524, 3, '01420414510', 1, 1, 0, '2017-05-08 10:09:48'),
    (1719, 12524, 4, '07494854592', 1, 1, 0, '2017-05-08 10:13:02'),
    (1720, 12525, 1, 'test@test.com', 1, 1, 0, '2017-05-12 10:20:18'),
    (1721, 12525, 2, '07343242342', 1, 1, 0, '2017-05-12 10:20:18'),
    (1722, 12525, 3, '01731123456', 1, 1, 0, '2017-05-12 10:20:18'),
    (1723, 12525, 4, '01734123456', 1, 1, 0, '2017-05-12 10:21:49'),
    (1724, 12526, 1, 'terry@terry.com', 1, 1, 0, '2017-05-12 21:51:34'),
    (1725, 12526, 2, '07123333333', 1, 1, 0, '2017-05-12 21:51:34'),
    (1726, 12526, 3, '03034334343', 1, 1, 0, '2017-05-12 21:51:34'),
    (1727, 12527, 1, 'tony@tester.com', 1, 0, 0, '2017-05-12 22:01:57'),
    (1728, 12527, 2, '07544444444', 1, 1, 0, '2017-05-12 22:01:57'),
    (1729, 12527, 3, '02086767676', 1, 1, 0, '2017-05-12 22:01:57'),
    (1730, 12528, 1, 'terry@tester.com', 1, 1, 0, '2017-05-12 22:08:22'),
    (1731, 12528, 2, '07332232222', 1, 1, 0, '2017-05-12 22:08:22'),
    (1732, 12528, 3, '02098888888', 1, 1, 0, '2017-05-12 22:08:22'),
    (1733, 12528, 4, '02089992929', 1, 1, 0, '2017-05-12 22:13:02'),
    (1734, 12529, 1, 'info@myemail.com', 1, 1, 0, '2017-05-12 23:14:28'),
    (1735, 12529, 2, '07086667656', 1, 1, 0, '2017-05-12 23:14:28'),
    (1736, 12529, 3, '02098887878', 1, 1, 0, '2017-05-12 23:14:28'),
    (1737, 12529, 4, '02087776767', 1, 1, 0, '2017-05-12 23:15:17'),
    (1738, 12530, 1, 'sgmudfgrray@live.co.uk', 1, 1, 0, '2017-05-16 10:05:04'),
    (1739, 12530, 2, '07771755011', 1, 1, 0, '2017-05-16 10:05:04'),
    (1740, 12530, 3, '01622817656', 1, 1, 0, '2017-05-16 10:05:04'),
    (1741, 12530, 4, '02037475718', 1, 1, 0, '2017-05-16 10:07:43'),
    (1748, 12533, 1, 'mbudfgper@undfgdk.co.uk', 1, 1, 0, '2017-05-17 10:55:23'),
    (1749, 12533, 2, '07000123456', 1, 1, 0, '2017-05-17 10:55:23'),
    (1750, 12533, 3, '02030000000', 1, 1, 0, '2017-05-17 10:55:23'),
    (1751, 12534, 1, '1@uncdfgdfgck.co.uk', 1, 1, 0, '2017-05-17 15:17:56'),
    (1752, 12534, 2, '07000000000', 1, 1, 0, '2017-05-17 15:17:56'),
    (1753, 12534, 3, '02000000000', 1, 1, 0, '2017-05-17 15:17:56'),
    (1754, 12535, 1, '2@uncldfgdfk.co.uk', 1, 1, 0, '2017-05-17 15:45:02'),
    (1755, 12535, 2, '07000000001', 1, 1, 0, '2017-05-17 15:45:02'),
    (1756, 12535, 3, '02030000001', 1, 1, 0, '2017-05-17 15:45:02'),
    (1757, 12536, 1, 'test@stra890tedfgster.com', 1, 1, 0, '2017-05-24 05:13:46'),
    (1758, 12536, 2, '07332232277', 1, 1, 0, '2017-05-24 05:13:46'),
    (1759, 12536, 3, '02998879888', 1, 1, 0, '2017-05-24 05:13:46'),
    (1760, 12539, 1, 'test@test34324.com', 1, 1, 0, '2017-09-25 07:33:58'),
    (1763, 12540, 1, 'test@gmail.com', 1, 1, 0, '2017-10-09 11:20:00'),
    (1764, 12540, 2, '07123456789', 1, 1, 0, '2017-10-09 11:20:00'),
    (1765, 12540, 3, '01123456789', 1, 1, 0, '2017-10-09 11:20:00'),
    (1766, 12540, 4, '01234567895', 1, 1, 0, '2017-10-09 11:31:23'),
    (1770, 12540, 1, 'gargruchi12@gmail.com', 0, 0, 30, '2017-10-09 13:07:24'),
    (1771, 12542, 1, 'rgupta@uddfgdfgk.com', 1, 1, 0, '2017-10-09 13:09:49'),
    (1772, 12542, 2, '07507245489', 1, 1, 0, '2017-10-09 13:09:49'),
    (1773, 12542, 3, '01282560110', 1, 1, 0, '2017-10-09 13:09:49'),
    (1774, 12542, 4, '01282560110', 1, 1, 0, '2017-10-09 13:11:48'),
    (1775, 12542, 4, '01324564564', 1, 1, 0, '2017-10-09 13:14:34'),
    (1776, 12527, 1, 'rob@nedgfdfgdls.co.uk', 1, 1, 31, '2017-10-12 09:42:13');

    CREATE TABLE `loans` (
      `loan_id` int(11) NOT NULL,
      `client_id` int(11) NOT NULL,
      `product_id` int(11) NOT NULL DEFAULT '1',
      `amount` decimal(7,2) NOT NULL DEFAULT '0.00',
      `interest_percent` decimal(7,2) DEFAULT '0.00' COMMENT 'Daily Interest Rate',
      `instalments` int(3) DEFAULT NULL,
      `fraud_id` int(11) NOT NULL DEFAULT '1',
      `code` varchar(4) NOT NULL,
      `code_sent` tinyint(1) NOT NULL DEFAULT '0',
      `signed` tinyint(1) DEFAULT '0',
      `signed_date` date DEFAULT NULL,
      `approved_amount` decimal(7,2) NOT NULL DEFAULT '0.00',
      `approved_user_id` int(11) DEFAULT NULL,
      `rtd` tinyint(1) NOT NULL DEFAULT '0',
      `rtd_date` datetime DEFAULT NULL,
      `deposited_amount` decimal(7,2) NOT NULL DEFAULT '0.00',
      `deposited_date` date DEFAULT NULL,
      `depsosited_user_id` int(11) DEFAULT NULL,
      `due_date` date DEFAULT NULL,
      `assigned_user_id` int(11) DEFAULT '0',
      `content_message_id` int(11) DEFAULT NULL,
      `balance` decimal(7,2) DEFAULT NULL,
      `payout` decimal(7,2) DEFAULT NULL,
      `repaid` tinyint(1) NOT NULL DEFAULT '0',
      `repaid_date` date DEFAULT NULL,
      `writeoff` tinyint(1) NOT NULL DEFAULT '0',
      `writeoff_date` date DEFAULT NULL,
      `writeoff_user_id` int(11) DEFAULT NULL,
      `apr` decimal(10,2) DEFAULT NULL,
      `sweep` tinyint(1) NOT NULL DEFAULT '1',
      `test` tinyint(1) NOT NULL DEFAULT '0',
      `ip` varchar(20) DEFAULT NULL,
      `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    INSERT INTO `loans` (`loan_id`, `client_id`, `product_id`, `amount`, `interest_percent`, `instalments`, `fraud_id`, `code`, `code_sent`, `signed`, `signed_date`, `approved_amount`, `approved_user_id`, `rtd`, `rtd_date`, `deposited_amount`, `deposited_date`, `depsosited_user_id`, `due_date`, `assigned_user_id`, `content_message_id`, `balance`, `payout`, `repaid`, `repaid_date`, `writeoff`, `writeoff_date`, `writeoff_user_id`, `apr`, `sweep`, `test`, `ip`, `created`) VALUES
    (9549, 12516, 4, '200.00', '0.80', 6, 1, '7812', 0, 1, '2017-02-27', '200.00', 1, 0, NULL, '200.00', '2017-01-03', 1, NULL, 1, 4, '90.22', '90.22', 0, NULL, 0, NULL, NULL, '1266.04', 1, 0, NULL, '2017-02-27 01:08:26'),
    (9550, 12517, 4, '100.00', '0.80', 6, 1, '7521', 0, 0, NULL, '0.00', NULL, 0, NULL, '0.00', NULL, NULL, NULL, 18, NULL, '0.00', NULL, 0, NULL, 0, NULL, NULL, NULL, 1, 0, NULL, '2017-04-21 08:59:16'),
    (9551, 12518, 4, '100.00', '0.80', 6, 1, '7146', 0, 0, NULL, '0.00', NULL, 0, NULL, '0.00', NULL, NULL, NULL, 0, NULL, NULL, NULL, 0, NULL, 0, NULL, NULL, NULL, 1, 0, NULL, '2017-04-21 09:37:08'),
    (9552, 12519, 4, '100.00', '0.80', 6, 1, '9524', 0, 0, NULL, '0.00', 0, 0, NULL, '0.00', NULL, NULL, NULL, 0, 2, NULL, NULL, 0, NULL, 0, NULL, NULL, NULL, 1, 0, NULL, '2017-04-21 09:42:53'),
    (9553, 12520, 1, '100.00', '0.80', 3, 1, '2597', 0, 0, NULL, '0.00', 0, 0, NULL, '0.00', NULL, NULL, NULL, 0, 2, NULL, NULL, 0, NULL, 0, NULL, NULL, NULL, 1, 0, NULL, '2017-04-21 19:17:29'),
    (9554, 12521, 1, '100.00', '0.80', 3, 1, '8057', 0, 0, NULL, '0.00', 0, 0, NULL, '0.00', NULL, NULL, NULL, 0, 2, NULL, NULL, 0, NULL, 0, NULL, NULL, NULL, 1, 0, NULL, '2017-05-01 08:23:04'),
    (9555, 12516, 4, '200.00', '0.80', 6, 1, '7812', 0, 1, '2017-03-27', '200.00', 1, 0, NULL, '200.00', '2017-02-03', 1, NULL, 1, 4, '508.80', NULL, 0, NULL, 0, NULL, NULL, '1266.04', 1, 0, NULL, '2017-03-27 00:08:26'),
    (9556, 12522, 1, '100.00', '0.80', 3, 1, '2976', 0, 0, NULL, '0.00', NULL, 0, NULL, '0.00', NULL, NULL, NULL, 0, NULL, NULL, NULL, 0, NULL, 0, NULL, NULL, NULL, 1, 0, NULL, '2017-05-02 16:51:06'),
    (9557, 12523, 1, '100.00', '0.80', 3, 1, '4366', 0, 0, NULL, '0.00', 0, 0, NULL, '0.00', NULL, NULL, NULL, 0, 2, NULL, NULL, 0, NULL, 0, NULL, NULL, NULL, 1, 0, NULL, '2017-05-08 09:46:24'),
    (9558, 12524, 1, '100.00', '0.80', 3, 1, '9040', 0, 0, NULL, '100.00', 18, 0, NULL, '0.00', NULL, NULL, NULL, 0, 2, NULL, NULL, 0, NULL, 0, NULL, NULL, NULL, 1, 0, NULL, '2017-05-08 10:09:48'),
    (9559, 12525, 1, '250.00', '0.80', 3, 1, '4416', 0, 0, NULL, '0.00', 0, 0, NULL, '0.00', NULL, NULL, NULL, 0, 2, NULL, NULL, 0, NULL, 0, NULL, NULL, NULL, 1, 0, NULL, '2017-05-12 10:20:18'),
    (9560, 12526, 1, '175.00', '0.80', 3, 1, '2242', 0, 0, NULL, '0.00', NULL, 0, NULL, '0.00', NULL, NULL, NULL, 0, NULL, NULL, NULL, 0, NULL, 0, NULL, NULL, NULL, 1, 0, NULL, '2017-05-12 21:51:34'),
    (9561, 12527, 1, '100.00', '0.80', 3, 1, '2623', 0, 0, NULL, '0.00', NULL, 0, NULL, '0.00', NULL, NULL, NULL, 0, NULL, NULL, NULL, 0, NULL, 0, NULL, NULL, NULL, 1, 0, NULL, '2017-05-12 22:01:57'),
    (9562, 12528, 1, '100.00', '0.80', 3, 1, '4932', 1, 1, '2017-05-12', '100.00', 1, 1, '2017-05-13 05:14:06', '0.00', NULL, NULL, NULL, 0, 2, NULL, NULL, 0, NULL, 0, NULL, NULL, NULL, 1, 0, NULL, '2017-05-12 22:08:22'),
    (9563, 12529, 1, '150.00', '0.80', 3, 1, '8136', 0, 0, NULL, '0.00', 0, 0, NULL, '0.00', NULL, NULL, NULL, 0, 2, NULL, NULL, 0, NULL, 0, NULL, NULL, NULL, 1, 0, NULL, '2017-05-12 23:14:28'),
    (9564, 12530, 1, '350.00', '0.80', 3, 1, '2248', 0, 0, NULL, '0.00', 0, 0, NULL, '0.00', NULL, NULL, NULL, 0, 2, NULL, NULL, 0, NULL, 0, NULL, NULL, NULL, 1, 0, NULL, '2017-05-16 10:05:04'),
    (9565, 12536, 1, '200.00', '0.80', 3, 1, '8079', 0, 0, NULL, '0.00', NULL, 0, NULL, '0.00', NULL, NULL, NULL, 0, NULL, NULL, NULL, 0, NULL, 0, NULL, NULL, NULL, 1, 0, NULL, '2017-05-24 05:13:46'),
    (9567, 12539, 1, '300.00', '0.80', 3, 1, '8223', 0, 0, NULL, '0.00', NULL, 0, NULL, '0.00', NULL, NULL, NULL, 0, NULL, NULL, NULL, 0, NULL, 0, NULL, NULL, NULL, 1, 0, NULL, '2017-09-25 07:33:58'),
    (9568, 12540, 1, '375.00', '0.80', 3, 1, '9123', 0, 0, NULL, '0.00', 0, 0, NULL, '0.00', NULL, NULL, NULL, 0, 2, NULL, NULL, 0, NULL, 0, NULL, NULL, NULL, 1, 0, NULL, '2017-10-09 11:20:00'),
    (9570, 12542, 1, '100.00', '0.80', 3, 1, '4470', 0, 0, NULL, '0.00', NULL, 0, NULL, '0.00', NULL, NULL, NULL, 0, NULL, NULL, NULL, 0, NULL, 0, NULL, NULL, NULL, 1, 0, NULL, '2017-10-09 13:09:49');

    ALTER TABLE `clients`
      ADD PRIMARY KEY (`client_id`),
      ADD KEY `fname` (`fname`),
      ADD KEY `created` (`created`),
      ADD KEY `lname` (`lname`),
      ADD KEY `title_id` (`title_id`),
      ADD KEY `username` (`username`),
      ADD KEY `clientref` (`clientref`),
      ADD KEY `dob` (`dob`),
      ADD KEY `market_consent` (`market_consent`),
      ADD KEY `blacklisted` (`blacklisted`),
      ADD KEY `market_broker` (`market_broker`),
      ADD KEY `marital_id` (`marital_id`),
      ADD KEY `status_difficulty_id` (`status_difficulty_id`);

    ALTER TABLE `clients_addresses`
      ADD PRIMARY KEY (`client_address_id`),
      ADD KEY `created` (`created`),
      ADD KEY `postcode` (`postcode`),
      ADD KEY `client_id` (`client_id`),
      ADD KEY `move_in_date` (`movein_date`),
      ADD KEY `current` (`current`),
      ADD KEY `home_status_id` (`home_status_id`);

    ALTER TABLE `clients_banks`
      ADD PRIMARY KEY (`client_bank_id`),
      ADD KEY `created` (`created`),
      ADD KEY `sort_code` (`sort_code`),
      ADD KEY `bank_id` (`bank_id`),
      ADD KEY `client_id` (`client_id`),
      ADD KEY `active` (`active`);

    ALTER TABLE `clients_coms`
      ADD PRIMARY KEY (`client_coms_id`),
      ADD KEY `client_id` (`client_id`),
      ADD KEY `value` (`value`),
      ADD KEY `active` (`active`),
      ADD KEY `created` (`created`),
      ADD KEY `coms_id` (`coms_id`),
      ADD KEY `primary_2` (`main`),
      ADD KEY `user_id` (`user_id`);

    ALTER TABLE `loans`
      ADD PRIMARY KEY (`loan_id`),
      ADD KEY `client_id` (`client_id`),
      ADD KEY `product_id` (`product_id`),
      ADD KEY `depsoited_user_id` (`depsosited_user_id`),
      ADD KEY `deposited_date` (`deposited_date`),
      ADD KEY `approved_user_id` (`approved_user_id`),
      ADD KEY `deposited_amount` (`deposited_amount`),
      ADD KEY `created` (`created`),
      ADD KEY `repaid` (`repaid`),
      ADD KEY `repaid_date` (`repaid_date`),
      ADD KEY `sweep` (`sweep`),
      ADD KEY `due_date` (`due_date`),
      ADD KEY `ip` (`ip`),
      ADD KEY `signed` (`signed`),
      ADD KEY `signed_date` (`signed_date`),
      ADD KEY `fraud_id` (`fraud_id`),
      ADD KEY `assigned_user_id` (`assigned_user_id`),
      ADD KEY `code_sent` (`code_sent`),
      ADD KEY `test` (`test`),
      ADD KEY `content_message_id` (`content_message_id`),
      ADD KEY `rtd` (`rtd`),
      ADD KEY `rtd_date` (`rtd_date`),
      ADD KEY `rtd_2` (`rtd`,`deposited_amount`),
      ADD KEY `writeoff` (`writeoff`),
      ADD KEY `writeoff_date` (`writeoff_date`),
      ADD KEY `payout` (`payout`),
      ADD KEY `balance` (`balance`),
      ADD KEY `writeoff_user_id` (`writeoff_user_id`);

    ALTER TABLE `clients`
      MODIFY `client_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12543;

    ALTER TABLE `clients_addresses`
      MODIFY `client_address_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=605;

    ALTER TABLE `clients_banks`
      MODIFY `client_bank_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=564;

    ALTER TABLE `clients_coms`
      MODIFY `client_coms_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1777;

    ALTER TABLE `loans`
      MODIFY `loan_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9571;

    ALTER TABLE `clients_addresses`
      ADD CONSTRAINT `clients_addresses_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`client_id`) ON DELETE CASCADE ON UPDATE CASCADE,

    ALTER TABLE `clients_banks`
      ADD CONSTRAINT `clients_banks_ibfk_2` FOREIGN KEY (`client_id`) REFERENCES `clients` (`client_id`) ON DELETE CASCADE ON UPDATE CASCADE;

    ALTER TABLE `clients_coms`
      ADD CONSTRAINT `clients_coms_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`client_id`) ON DELETE CASCADE ON UPDATE CASCADE,

    ALTER TABLE `loans`
      ADD CONSTRAINT `loans_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`client_id`) ON DELETE CASCADE ON UPDATE CASCADE,

Body:
Your query looks like selecting all table fields:

Inefficient:

It include all the records before it display the fields you wanted.

It making your query become too slow when the times comes you got a millions of data.

Performance problem.

To Resolve:

Just select specific fields that you need like:

Avoid using Select *

select a.f1,a.f2,b.f3,a.f4,b.f5 from
(select f1,f2,f4 from table1) as a
left join
(select f1, f3,f5 from table2) as b
on a.f1 = b.f1

as for me this is to be called table query optimizing

Off course All indexes of your tables are done and created.


问:
Table on smaller screen to appear as list with several columns collapsed and shown on accordion
I have a table in bootstrap as follows:

```<code><table class="table table-striped">
<thead>
<tr>
  <th>Date</th>
  <th>Name</th>
  <th>LastName</th>
  <th>Color</th>
   <th>Car</th>
  <th>Year</th>
  <th>Price</th>

 </tr>
 </thead>
 <tbody>
<tr>
  <td>01/02/2017</td>
  <td>Dan</td>
  <td>Green</td>
  <td>Blue</td>
  <td>BMW</td>
  <td>2016</td>
  <td>100k</td>
</tr>
    <tr>
  <td>01/02/2017</td>
  <td>Dan</td>
  <td>Green</td>
  <td>Blue</td>
  <td>BMW</td>
  <td>2016</td>
  <td>100k</td>
</tr>
    <tr>
  <td>01/02/2017</td>
  <td>Dan</td>
  <td>Green</td>
  <td>Blue</td>
  <td>BMW</td>
  <td>2016</td>
  <td>100k</td>
</tr>
    <tr>
  <td>01/02/2017</td>
  <td>Dan</td>
  <td>Green</td>
  <td>Blue</td>
  <td>BMW</td>
  <td>2016</td>
  <td>100k</td>
</tr>
    <tr>
  <td>01/02/2017</td>
  <td>Dan</td>
  <td>Green</td>
  <td>Blue</td>
  <td>BMW</td>
  <td>2016</td>
  <td>100k</td>
</tr>

 </tbody>
 </table>
</code>```

What I want to do is that on a breakpoint, say a screen smaller than 768px, to display the table like this :

```<code>DATE:01/05/2017
car:BMW
</code>```

And then on click on this row to display:

```<code> Name:Dan
 LastName:Green
 Color:blue
 Year:2016
</code>```

Is there a way to achieve this with bootstrap?
Or maybe I shouldn't have everything in a table and instead have elements in a list, and make them display as table (cells) and on smaller screens to align them?
Because at first I thought of creating two views and display/hide them according to the screen size, because I dont know how to do it this way.
I'm a beginner so I apologize in advance.
I don't know which are best practices and how to achieve this, preferably using css only. Whereas JS only on click to hide and show that part.

Here is a js fiddle Bootstrap Table

Any help is appreciated.

答:
For the layout you should have a look at the bootstrap grid. https://getbootstrap.com/docs/4.0/layout/grid/

It does not technically use "table" but divs and you set how many columns you want a div to take for each screen size. When I started with bootstap I watched though this nice youtube tutorial that explained it well https://www.youtube.com/watch?v=g3LRU3JF-rQ

Body:
I have a table in bootstrap as follows:

  Date
  Name
  LastName
  Color
   Car
  Year
  Price

  01/02/2017
  Dan
  Green
  Blue
  BMW
  2016
  100k

  01/02/2017
  Dan
  Green
  Blue
  BMW
  2016
  100k

  01/02/2017
  Dan
  Green
  Blue
  BMW
  2016
  100k

  01/02/2017
  Dan
  Green
  Blue
  BMW
  2016
  100k

  01/02/2017
  Dan
  Green
  Blue
  BMW
  2016
  100k

What I want to do is that on a breakpoint, say a screen smaller than 768px, to display the table like this :

DATE:01/05/2017
car:BMW

And then on click on this row to display:

 Name:Dan
 LastName:Green
 Color:blue
 Year:2016

Is there a way to achieve this with bootstrap?
Or maybe I shouldn't have everything in a table and instead have elements in a list, and make them display as table (cells) and on smaller screens to align them?
Because at first I thought of creating two views and display/hide them according to the screen size, because I dont know how to do it this way.
I'm a beginner so I apologize in advance.
I don't know which are best practices and how to achieve this, preferably using css only. Whereas JS only on click to hide and show that part.

Here is a js fiddle Bootstrap Table

Any help is appreciated.


答:
One vectorized way would be expanding dimensions and thus leverage broadcasting -

```<code>np.tile((indices[:,None,None] == np.arange(n)[:,None]), m)
</code>```

Sample run -

```<code>In [755]: # Sample Setup
     ...: indices = np.array([2,3,1])
     ...:
     ...: k = 3
     ...: n = 4
     ...: m = 2
     ...: mask = np.zeros((k, n, m),dtype=bool)
     ...: for i in range(k):
     ...:     mask[i][indices[i]] = 1

In [756]: out = np.tile((indices[:,None,None] == np.arange(n)[:,None]), m)

In [757]: np.allclose(out, mask)
Out[757]: True
</code>```

To port over to tensorflow, we have the counterparts there :

tf.expand_dims and tf.tile.

Title: How to mask specific slices for each row in a tensor

Body:
I'd like to modify specific vectors in a 3D tensor, according to a list of positions (indices):

#indices: 1D vector of positions, indices.shape: (k)

mask = np.zeros(k, n, m)
for i in range(k):
  mask[i][indices[i]] = 1

This mask will be applied to another 3D tensor (same shape), where I want to preserve specific vectors, and zero out the rest.

What's the best way to construct such a mask in TensorFlow? I can do it with a loop using an assign op, but I'd like to find a more elegant solution. Perhaps using tf.scatter_nd?

EDIT: example:

>>> mask_before
array([[[ 0.,  0.,  0.,  0.],
        [ 0.,  0.,  0.,  0.],
        [ 0.,  0.,  0.,  0.],
        [ 0.,  0.,  0.,  0.],
        [ 0.,  0.,  0.,  0.]],

       [[ 0.,  0.,  0.,  0.],
        [ 0.,  0.,  0.,  0.],
        [ 0.,  0.,  0.,  0.],
        [ 0.,  0.,  0.,  0.],
        [ 0.,  0.,  0.,  0.]],

       [[ 0.,  0.,  0.,  0.],
        [ 0.,  0.,  0.,  0.],
        [ 0.,  0.,  0.,  0.],
        [ 0.,  0.,  0.,  0.],
        [ 0.,  0.,  0.,  0.]]])

>>> indices
array([2, 1, 4])

>>> mask_after
array([[[ 0.,  0.,  0.,  0.],
        [ 0.,  0.,  0.,  0.],
        [ 1.,  1.,  1.,  1.],
        [ 0.,  0.,  0.,  0.],
        [ 0.,  0.,  0.,  0.]],

       [[ 0.,  0.,  0.,  0.],
        [ 1.,  1.,  1.,  1.],
        [ 0.,  0.,  0.,  0.],
        [ 0.,  0.,  0.,  0.],
        [ 0.,  0.,  0.,  0.]],

       [[ 0.,  0.,  0.,  0.],
        [ 0.,  0.,  0.,  0.],
        [ 0.,  0.,  0.,  0.],
        [ 0.,  0.,  0.,  0.],
        [ 1.,  1.,  1.,  1.]]])

Body:
One vectorized way would be expanding dimensions and thus leverage broadcasting -

np.tile((indices[:,None,None] == np.arange(n)[:,None]), m)

Sample run -

In [755]: # Sample Setup
     ...: indices = np.array([2,3,1])
     ...:
     ...: k = 3
     ...: n = 4
     ...: m = 2
     ...: mask = np.zeros((k, n, m),dtype=bool)
     ...: for i in range(k):
     ...:     mask[i][indices[i]] = 1

In [756]: out = np.tile((indices[:,None,None] == np.arange(n)[:,None]), m)

In [757]: np.allclose(out, mask)
Out[757]: True

To port over to tensorflow, we have the counterparts there :

tf.expand_dims and tf.tile.


问:
catching a deadlock in a simple odd-even sending
I'm trying to solve a simple problem with MPI, my implementation is MPICH2 and my code is in fortran. I have used the blocking send and receive, the idea is so simple but when I run it it crashes!!! I have absolutely no idea what is wrong? can anyone make quote on this issue please? there is a piece of the code:

```<code>integer, parameter :: IM=100, JM=100
REAL, ALLOCATABLE  :: T(:,:), TF(:,:)

CALL MPI_COMM_RANK(MPI_COMM_WORLD,RNK,IERR)
CALL MPI_COMM_SIZE(MPI_COMM_WORLD,SIZ,IERR)

prv = rnk-1
nxt = rnk+1
LIM = INT(IM/SIZ)

IF (rnk==0) THEN
    ALLOCATE(TF(IM,JM))
    prv = MPI_PROC_NULL
ELSEIF(rnk==siz-1) THEN
    NXT = MPI_PROC_NULL
    LIM = LIM+MOD(IM,SIZ)
END IF

IF (MOD(RNK,2)==0) THEN
    CALL MPI_SEND(T(2,:),JM+2,MPI_REAL,PRV,10,MPI_COMM_WORLD,IERR)
    CALL MPI_RECV(T(1,:),JM+2,MPI_REAL,PRV,20,MPI_COMM_WORLD,STAT,IERR)
ELSE
    CALL MPI_RECV(T(LIM+2,:),JM+2,MPI_REAL,NXT,10,MPI_COMM_WORLD,STAT,IERR)
    CALL MPI_SEND(T(LIM+1,:),JM+2,MPI_REAL,NXT,20,MPI_COMM_WORLD,IERR)
END IF
</code>```

as I understood even processes are not receiving anything while the odd ones finish sending successfully, in some cases when I added some print to observe what is going on I saw that the variable NXT is changing during the sending procedure!!! for example all the odd process was sending message to process 0 not their next one!

答:
I can't see the whole program, but some observation on what I can see:

1) make sure that rnk, size, and prv are integers.  Likely, prv is real (by default
  typing rules) and you are sending a real to an integer, so tags don't match, hence deadlock.

2) I'd use sendrcv rather than send / recv ; recv / send code section. Two sendrecv's are cleaner ( 2 lines of code vs. 7), guaranteed not to deadlock, and is faster when you have bi-directional links (almost always true.)

ContentLicense: CC BY-SA 2.5

Body:
I'm trying to solve a simple problem with MPI, my implementation is MPICH2 and my code is in fortran. I have used the blocking send and receive, the idea is so simple but when I run it it crashes!!! I have absolutely no idea what is wrong? can anyone make quote on this issue please? there is a piece of the code:

integer, parameter :: IM=100, JM=100
REAL, ALLOCATABLE  :: T(:,:), TF(:,:)

CALL MPI_COMM_RANK(MPI_COMM_WORLD,RNK,IERR)
CALL MPI_COMM_SIZE(MPI_COMM_WORLD,SIZ,IERR)

prv = rnk-1
nxt = rnk+1
LIM = INT(IM/SIZ)

IF (rnk==0) THEN
    ALLOCATE(TF(IM,JM))
    prv = MPI_PROC_NULL
ELSEIF(rnk==siz-1) THEN
    NXT = MPI_PROC_NULL
    LIM = LIM+MOD(IM,SIZ)
END IF

IF (MOD(RNK,2)==0) THEN
    CALL MPI_SEND(T(2,:),JM+2,MPI_REAL,PRV,10,MPI_COMM_WORLD,IERR)
    CALL MPI_RECV(T(1,:),JM+2,MPI_REAL,PRV,20,MPI_COMM_WORLD,STAT,IERR)
ELSE
    CALL MPI_RECV(T(LIM+2,:),JM+2,MPI_REAL,NXT,10,MPI_COMM_WORLD,STAT,IERR)
    CALL MPI_SEND(T(LIM+1,:),JM+2,MPI_REAL,NXT,20,MPI_COMM_WORLD,IERR)
END IF

as I understood even processes are not receiving anything while the odd ones finish sending successfully, in some cases when I added some print to observe what is going on I saw that the variable NXT is changing during the sending procedure!!! for example all the odd process was sending message to process 0 not their next one!


问:
Early-stopping while training neural network in scikit-learn
This questions is very specific to the Python library scikit-learn. Please let me know if it's a better idea to post it somewhere else. Thanks!

Now the question...

I have a feed-forward neural network class ffnn based on BaseEstimator which I train with SGD. It's working fine, and I can also train it in parallel using GridSearchCV().

Now I want to implement early stopping in the function ffnn.fit() but for this I also need access to the validation data of the fold. One way of doing this is to change the line in sklearn.grid_search.fit_grid_point() which says

```<code>clf.fit(X_train, y_train, **fit_params)
</code>```

into something like

```<code>clf.fit(X_train, y_train, X_test, y_test, **fit_params)
</code>```

and also change ffnn.fit() to take these arguments. This would also affect other classifiers in sklearn, which is a problem. I can avoid this by checking for some kind of a flag in fit_grid_point() which tells me when to call clf.fit() in either of the above two ways.

Can someone suggest a different way to do this where I don't have to edit any code in the sklearn library?

Alternatively, would it be right to further split X_train and y_train into train/validation sets randomly and check for a good stopping point, then re-train the model on all of X_train?

Thanks!

答:
There are two ways:

First:

While taking a x_train and x_test split. You can take a 0.1 split from x_train and keep it for validation x_dev:

```<code>x_train, x_test, y_train, y_test = train_test_split(data_x, data_y, test_size=0.25)

x_train, x_dev, y_train, y_dev = train_test_split(x_train, y_train, test_size=0.1)

clf = GridSearchCV(YourEstimator(), param_grid=param_grid,)
clf.fit(x_train, y_train, x_dev, y_dev)
</code>```

And your estimator will look like the following and implement early stopping with x_dev, y_dev

```<code>class YourEstimator(BaseEstimator, ClassifierMixin):
    def __init__(self, param1, param2):
        # perform initialization
        #

    def fit(self, x, y, x_dev=None, y_dev=None):
        # perform training with early stopping
        #
</code>```

Second

You would not perform the second split on x_train, but would take out the dev set in the fit method of the Estimator

```<code>x_train, x_test, y_train, y_test = train_test_split(data_x, data_y, test_size=0.25)

clf = GridSearchCV(YourEstimator(), param_grid=param_grid)
clf.fit(x_train, y_train)
</code>```

And your estimator will look like the following:

```<code>class YourEstimator(BaseEstimator, ClassifierMixin):
    def __init__(self, param1, param2):
        # perform initialization
        #

    def fit(self, x, y):
        # perform training with early stopping
        x_train, x_dev, y_train, y_dev = train_test_split(x, y,
                                                         test_size=0.1)
</code>```

Body:
This questions is very specific to the Python library scikit-learn. Please let me know if it's a better idea to post it somewhere else. Thanks!

Now the question...

I have a feed-forward neural network class ffnn based on BaseEstimator which I train with SGD. It's working fine, and I can also train it in parallel using GridSearchCV().

Now I want to implement early stopping in the function ffnn.fit() but for this I also need access to the validation data of the fold. One way of doing this is to change the line in sklearn.grid_search.fit_grid_point() which says

clf.fit(X_train, y_train, **fit_params)

into something like

clf.fit(X_train, y_train, X_test, y_test, **fit_params)

and also change ffnn.fit() to take these arguments. This would also affect other classifiers in sklearn, which is a problem. I can avoid this by checking for some kind of a flag in fit_grid_point() which tells me when to call clf.fit() in either of the above two ways.

Can someone suggest a different way to do this where I don't have to edit any code in the sklearn library?

Alternatively, would it be right to further split X_train and y_train into train/validation sets randomly and check for a good stopping point, then re-train the model on all of X_train?

Thanks!

Body:
There are two ways:

First:

While taking a x_train and x_test split. You can take a 0.1 split from x_train and keep it for validation x_dev:

x_train, x_test, y_train, y_test = train_test_split(data_x, data_y, test_size=0.25)

x_train, x_dev, y_train, y_dev = train_test_split(x_train, y_train, test_size=0.1)

clf = GridSearchCV(YourEstimator(), param_grid=param_grid,)
clf.fit(x_train, y_train, x_dev, y_dev)

And your estimator will look like the following and implement early stopping with x_dev, y_dev

class YourEstimator(BaseEstimator, ClassifierMixin):
    def __init__(self, param1, param2):
        # perform initialization
        #

    def fit(self, x, y, x_dev=None, y_dev=None):
        # perform training with early stopping
        #

Second

You would not perform the second split on x_train, but would take out the dev set in the fit method of the Estimator

x_train, x_test, y_train, y_test = train_test_split(data_x, data_y, test_size=0.25)

clf = GridSearchCV(YourEstimator(), param_grid=param_grid)
clf.fit(x_train, y_train)

And your estimator will look like the following:

class YourEstimator(BaseEstimator, ClassifierMixin):
    def __init__(self, param1, param2):
        # perform initialization
        #

    def fit(self, x, y):
        # perform training with early stopping
        x_train, x_dev, y_train, y_dev = train_test_split(x, y,
                                                         test_size=0.1)


问:
Python MySQL Connector Error Diagnosis

CONTEXT

Ubuntu 16.04: Python MySQL Connector

OBSERVATIONS

The cursor.execute statement appends the target record to a MySQL 5.5.8 database.  Attempts to append to MySQL 8.0.3 result in the error below:

```<code>>>> cursor.execute(add_scan, data_scan)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/lib/python2.7/dist-packages/mysql/connector/cursor.py", line 551, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "/usr/local/lib/python2.7/dist-packages/mysql/connector/connection.py", line 490, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "/usr/local/lib/python2.7/dist-packages/mysql/connector/connection.py", line 395, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.DatabaseError: 1364 (HY000): Field 'scantime' doesn't have a default value
</code>```

Then this line is commented out, then the .py script completes execution without any sign of error.

QUESTIONS

Is the issue simply that the newer MySQL 8 database is incompatible?

Can the MySQL connector be update somehow?

答:
One of your servers is running in strict mode by default and the other not. If a server runs in strict mode (or you set it in your connection) and you try to insert a NULL value into a column defined as NOT NULL you will get #1364 error. Without strict mode your NULL value will be replaced with empty string or 0.

Example:

```<code>CREATE TABLE `test_tbl` (
 `id` int(11) NOT NULL,
 `someint` int(11) NOT NULL,
 `sometext` varchar(255) NOT NULL,
 `somedate` datetime NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

SET sql_mode = '';
INSERT INTO test_tbl(id) VALUES(1);
SELECT * FROM test_tbl;
+----+---------+----------+---------------------+
| id | someint | sometext | somedate            |
+----+---------+----------+---------------------+
|  1 |       0 |          | 0000-00-00 00:00:00 |
+----+---------+----------+---------------------+
SET sql_mode = 'STRICT_ALL_TABLES';
INSERT INTO test_tbl(id) VALUES(2);
#1364 - Field 'someint' doesn't have a default value
</code>```

Source : MySQL INSERT without having to specify every non-default field (#1067 - Invalid default value for 'table')

Fix: change that field to remove not null or set it to a default value such as CURRENT_TIMESTAMP

Title: Python MySQL Connector Error Diagnosis

Body:
CONTEXT

Ubuntu 16.04: Python MySQL Connector

OBSERVATIONS

The cursor.execute statement appends the target record to a MySQL 5.5.8 database.  Attempts to append to MySQL 8.0.3 result in the error below:

>>> cursor.execute(add_scan, data_scan)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/lib/python2.7/dist-packages/mysql/connector/cursor.py", line 551, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "/usr/local/lib/python2.7/dist-packages/mysql/connector/connection.py", line 490, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "/usr/local/lib/python2.7/dist-packages/mysql/connector/connection.py", line 395, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.DatabaseError: 1364 (HY000): Field 'scantime' doesn't have a default value

Then this line is commented out, then the .py script completes execution without any sign of error.

QUESTIONS

Is the issue simply that the newer MySQL 8 database is incompatible?

Can the MySQL connector be update somehow?

Body:
One of your servers is running in strict mode by default and the other not. If a server runs in strict mode (or you set it in your connection) and you try to insert a NULL value into a column defined as NOT NULL you will get #1364 error. Without strict mode your NULL value will be replaced with empty string or 0.

Example:

CREATE TABLE `test_tbl` (
 `id` int(11) NOT NULL,
 `someint` int(11) NOT NULL,
 `sometext` varchar(255) NOT NULL,
 `somedate` datetime NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

SET sql_mode = '';
INSERT INTO test_tbl(id) VALUES(1);
SELECT * FROM test_tbl;
+----+---------+----------+---------------------+
| id | someint | sometext | somedate            |
+----+---------+----------+---------------------+
|  1 |       0 |          | 0000-00-00 00:00:00 |
+----+---------+----------+---------------------+
SET sql_mode = 'STRICT_ALL_TABLES';
INSERT INTO test_tbl(id) VALUES(2);
#1364 - Field 'someint' doesn't have a default value

Source : MySQL INSERT without having to specify every non-default field (#1067 - Invalid default value for 'table')

Fix: change that field to remove not null or set it to a default value such as CURRENT_TIMESTAMP


答:
In standard SQL it is impossible to group by a single column and still still output ever column from 2 tables (SELECT l.*, c.*). MySQL contains a non-standard "extension" which permits your query to run without an error message, but the truth is you should not rely on that extension. So you will need to adopt a more stringent approach to any query using group by.

You have also used a series of LEFT JOINS and the very reason we use that type of join is so that an unmatched row in the main table can still be returned. In other words, for each left joined table that table's columns can all be NULL. However your where clause overrides this possibility be insisting that com.value = 'llyamjamie@aol.com' or that bank.sort_code = '309374'. If you want more efficiency don't use left joins with those tables.

It appears as if you want one query to do almost anything, and at the same time be at peak efficiency. However these 2 objectives (flexibility and efficiency) compete.

You may find the following style of query more efficient yet sufficiently flexible. I don't know how you are building your queries, but I presume you have set of parameters, so below I just have some placeholders for whatever you might be using.

```<code>select l.*, c.* ## but specify the columns you actually need
from Loans l
inner join clients c ON l.client_id = c.client_id
where l.client_id IN (
   select client_id
   from clients where @clients is not null
   union all
   select client_id
   from clients_coms where clients_coms.value = 'llyamjamie@aol.com'
   union all
   select client_id
   from clients_addresses where @clients_addresses is not null
   union all
   select client_id
   from clients_emp where @clients_emp is not null
   union all
   select client_id
   from clients_banks where clients_banks.sort_code = '309374'
  )
</code>```

Ideally you would leave out the redundant parts of that query if you can. Note we do not need a group by through this approach. Please note however that this query has the effect ORs i.e.

```<code>`com.value = 'llyamjamie@aol.com'` OR `bank.sort_code = '309374'`
</code>```

Then you will need to look at how you are indexing all those tables. Every column used into join tables will be indexed I trust. For other indexes you need to look at the explain plans, but the most frequently searched columns are candidates for indexing.

Title: Querying Joined Tables

Body:
In standard SQL it is impossible to group by a single column and still still output ever column from 2 tables (SELECT l.*, c.*). MySQL contains a non-standard "extension" which permits your query to run without an error message, but the truth is you should not rely on that extension. So you will need to adopt a more stringent approach to any query using group by.

You have also used a series of LEFT JOINS and the very reason we use that type of join is so that an unmatched row in the main table can still be returned. In other words, for each left joined table that table's columns can all be NULL. However your where clause overrides this possibility be insisting that com.value = 'llyamjamie@aol.com' or that bank.sort_code = '309374'. If you want more efficiency don't use left joins with those tables.

It appears as if you want one query to do almost anything, and at the same time be at peak efficiency. However these 2 objectives (flexibility and efficiency) compete.

You may find the following style of query more efficient yet sufficiently flexible. I don't know how you are building your queries, but I presume you have set of parameters, so below I just have some placeholders for whatever you might be using.

select l.*, c.* ## but specify the columns you actually need
from Loans l
inner join clients c ON l.client_id = c.client_id
where l.client_id IN (
   select client_id
   from clients where @clients is not null
   union all
   select client_id
   from clients_coms where clients_coms.value = 'llyamjamie@aol.com'
   union all
   select client_id
   from clients_addresses where @clients_addresses is not null
   union all
   select client_id
   from clients_emp where @clients_emp is not null
   union all
   select client_id
   from clients_banks where clients_banks.sort_code = '309374'
  )

Ideally you would leave out the redundant parts of that query if you can. Note we do not need a group by through this approach. Please note however that this query has the effect ORs i.e.

`com.value = 'llyamjamie@aol.com'` OR `bank.sort_code = '309374'`

Then you will need to look at how you are indexing all those tables. Every column used into join tables will be indexed I trust. For other indexes you need to look at the explain plans, but the most frequently searched columns are candidates for indexing.


问:
Elegantly iterate over a CoreData relationship in swift?
I've got a ServiceGroup entity with a one-to-many relationship to ServiceItem called items. I want to iterate over the items but swift seems completely ignorant of the variable type because items is just defined as an NSOrderedSet?.

The only way I've found to be able to iterate over my items is that I have to cast them using the new case syntax added to for loops recently, but this seems ridiculous since the code -should- know what the type is already.

Anyone have suggestions so that I don't have to constantly case/coerce my relationship entities?

```<code>if let items = self.items {
    for case let item as ServiceItem in items {
        mins += item.calculateTotalMinutes()
    }
}
</code>```

答:
Something slightly more elegant (in my opinion) would be to use higher order functions like so:</p>

```<code>let minutes = items
        .map({$0 as? ServiceItem})
        .flatMap({$0})
        .reduce(0) { (result, serviceItem) -> Double in
            return result + serviceItem.calculateTotalMinutes()
    }
</code>```

Body:
I've got a ServiceGroup entity with a one-to-many relationship to ServiceItem called items. I want to iterate over the items but swift seems completely ignorant of the variable type because items is just defined as an NSOrderedSet?.

The only way I've found to be able to iterate over my items is that I have to cast them using the new case syntax added to for loops recently, but this seems ridiculous since the code -should- know what the type is already.

Anyone have suggestions so that I don't have to constantly case/coerce my relationship entities?

if let items = self.items {
    for case let item as ServiceItem in items {
        mins += item.calculateTotalMinutes()
    }
}

Body:
Something slightly more elegant (in my opinion) would be to use higher order functions like so:

let minutes = items
        .map({$0 as? ServiceItem})
        .flatMap({$0})
        .reduce(0) { (result, serviceItem) -> Double in
            return result + serviceItem.calculateTotalMinutes()
    }


问:
Constructor getting called twice React Component
The constructor of my react component is getting called twice but I can't figure out why. I am using react-redux to store the language of my app. I have a function that set the default language base on browser's language. LoginPage is the first component that gets render so I called my function in its constructor. Basically what it does is compare and dispatch an action. When I checked my state with redux developer tools I saw that it was getting dispatched twice. I printed dummy data in the constructor and it gets printed twice too.

LoginPage.js

```<code>import React from 'react';
import {connect} from 'react-redux';
import {startLogin} from '../actions/auth';
import {setLanguage} from '../actions/lang';

export class LoginPage extends React.Component{

  constructor(props){
    super(props);
    this.setDefaultLanguage();
    console.log('i am constructor');
  }

  changeLanguage = (e) => {
    const lan = e.target.value;
    this.props.setLanguage(lan);
  };

  setDefaultLanguage = () => {
    const defaultLanguage = navigator.language || navigator.userLanguage || 'en-US';

    if(defaultLanguage == 'es'){
      this.props.setLanguage(defaultLanguage);
    }else{
      this.props.setLanguage('en');
    }
  }

  render(){
    return(
      <div className="box-layout">
        <div className="box-layout__box">
          <h1 className="box-layout__title">Expensify</h1>
          <p>It\'s time to get your expenses under control.</p>
          <button className="button" onClick={this.props.startLogin}>Log in with google</button>
          <select className="select" onChange={this.changeLanguage}>
            <option value="en">English</option>
            <option value="es">Español</option>
          </select>
        </div>
      </div>
    )
  };
}

const mapDispatchToProps = (dispatch) => ({
  startLogin: () => dispatch(startLogin()),
  setLanguage: (language) => dispatch(setLanguage(language))
});

export default connect(undefined, mapDispatchToProps)( LoginPage);
</code>```

App.js

```<code>import React from 'react';
import ReactDom from 'react-dom';
import {Router, Route, Switch} from 'react-router-dom';
import createHistory from 'history/createBrowserHistory';
import ExpenseDashBoardPage from '../components/ExpenseDashBoardPage';
import AddExpensePage from '../components/AddExpensePage';
import EditExpensePage from '../components/EditExpensePage';
import NotFoundPage from '../components/NotFoundPage';
import LoginPage from '../components/LoginPage';
import PrivateRoute from './PrivateRoute';
import PublicRoute from './PublicRoute';

export const history = createHistory();

const AppRouter = () => (
  <Router history={history}>
    <div>
      <Switch>
        <PublicRoute path="/" component={LoginPage} exact={true} />
        <PrivateRoute path="/dashboard" component={ExpenseDashBoardPage} />
        <PrivateRoute path="/create" component={AddExpensePage} />
        <PrivateRoute path="/edit/:id" component={EditExpensePage} />
        <Route component={NotFoundPage} />
      </Switch>
    </div>

  </Router>
)

export default AppRouter;
</code>```

答:
If you check React Dev Tools of what is being rendered you'll see something like connect(App) as well as App by itself, so your console.log will be called twice. This is because connect is a higher order component.

If you move this.setDefaultLanguage() (and your console.log so you can see it in action) out of the constructor and into the componentDidMount life cycle hook, it should fix your problem.

Body:
The constructor of my react component is getting called twice but I can't figure out why. I am using react-redux to store the language of my app. I have a function that set the default language base on browser's language. LoginPage is the first component that gets render so I called my function in its constructor. Basically what it does is compare and dispatch an action. When I checked my state with redux developer tools I saw that it was getting dispatched twice. I printed dummy data in the constructor and it gets printed twice too.

LoginPage.js

import React from 'react';
import {connect} from 'react-redux';
import {startLogin} from '../actions/auth';
import {setLanguage} from '../actions/lang';

export class LoginPage extends React.Component{

  constructor(props){
    super(props);
    this.setDefaultLanguage();
    console.log('i am constructor');
  }

  changeLanguage = (e) => {
    const lan = e.target.value;
    this.props.setLanguage(lan);
  };

  setDefaultLanguage = () => {
    const defaultLanguage = navigator.language || navigator.userLanguage || 'en-US';

    if(defaultLanguage == 'es'){
      this.props.setLanguage(defaultLanguage);
    }else{
      this.props.setLanguage('en');
    }
  }

  render(){
    return(

Expensify

It\'s time to get your expenses under control.

          <button className="button" onClick={this.props.startLogin}>Log in with google</button>
          <select className="select" onChange={this.changeLanguage}>
            <option value="en">English</option>
            <option value="es">Español</option>
          </select>

    )
  };
}

const mapDispatchToProps = (dispatch) => ({
  startLogin: () => dispatch(startLogin()),
  setLanguage: (language) => dispatch(setLanguage(language))
});

export default connect(undefined, mapDispatchToProps)( LoginPage);

App.js

import React from 'react';
import ReactDom from 'react-dom';
import {Router, Route, Switch} from 'react-router-dom';
import createHistory from 'history/createBrowserHistory';
import ExpenseDashBoardPage from '../components/ExpenseDashBoardPage';
import AddExpensePage from '../components/AddExpensePage';
import EditExpensePage from '../components/EditExpensePage';
import NotFoundPage from '../components/NotFoundPage';
import LoginPage from '../components/LoginPage';
import PrivateRoute from './PrivateRoute';
import PublicRoute from './PublicRoute';

export const history = createHistory();

const AppRouter = () => (
  <Router history={history}>

      <Switch>
        <PublicRoute path="/" component={LoginPage} exact={true} />
        <PrivateRoute path="/dashboard" component={ExpenseDashBoardPage} />
        <PrivateRoute path="/create" component={AddExpensePage} />
        <PrivateRoute path="/edit/:id" component={EditExpensePage} />
        <Route component={NotFoundPage} />
      </Switch>

  </Router>
)

export default AppRouter;


问:
Is there a "null coalescing" operator in JavaScript?
Is there a null coalescing operator in Javascript?

For example, in C#, I can do this:

```<code>String someString = null;
var whatIWant = someString ?? "Cookies!";
</code>```

The best approximation I can figure out for Javascript is using the conditional operator:

```<code>var someString = null;
var whatIWant = someString ? someString : 'Cookies!';
</code>```

Which is sorta icky IMHO. Can I do better?

答:
Update

JavaScript now supports the nullish coalescing operator (??). It returns its right-hand-side operand when its left-hand-side operand is null or undefined, and otherwise returns its left-hand-side operand.

Old Answer

Please check compatibility before using it.

The JavaScript equivalent of the C# null coalescing operator (??) is using a logical OR (||):

```<code>var whatIWant = someString || "Cookies!";
</code>```

There are cases (clarified below) that the behaviour won't match that of C#, but this is the general, terse way of assigning default/alternative values in JavaScript.

Clarification

Regardless of the type of the first operand, if casting it to a Boolean results in false, the assignment will use the second operand. Beware of all the cases below:

```<code>alert(Boolean(null)); // false
alert(Boolean(undefined)); // false
alert(Boolean(0)); // false
alert(Boolean("")); // false
alert(Boolean("false")); // true -- gotcha! :)
</code>```

This means:

```<code>var whatIWant = null || new ShinyObject(); // is a new shiny object
var whatIWant = undefined || "well defined"; // is "well defined"
var whatIWant = 0 || 42; // is 42
var whatIWant = "" || "a million bucks"; // is "a million bucks"
var whatIWant = "false" || "no way"; // is "false"
</code>```

OwnerDisplayName: Daniel Schaffer

Body:
Is there a null coalescing operator in Javascript?

For example, in C#, I can do this:

String someString = null;
var whatIWant = someString ?? "Cookies!";

The best approximation I can figure out for Javascript is using the conditional operator:

var someString = null;
var whatIWant = someString ? someString : 'Cookies!';

Which is sorta icky IMHO. Can I do better?

LastEditorDisplayName: Daniel Schaffer

Body:
Update

JavaScript now supports the nullish coalescing operator (??). It returns its right-hand-side operand when its left-hand-side operand is null or undefined, and otherwise returns its left-hand-side operand.

Old Answer

Please check compatibility before using it.

The JavaScript equivalent of the C# null coalescing operator (??) is using a logical OR (||):

var whatIWant = someString || "Cookies!";

There are cases (clarified below) that the behaviour won't match that of C#, but this is the general, terse way of assigning default/alternative values in JavaScript.

Clarification

Regardless of the type of the first operand, if casting it to a Boolean results in false, the assignment will use the second operand. Beware of all the cases below:

alert(Boolean(null)); // false
alert(Boolean(undefined)); // false
alert(Boolean(0)); // false
alert(Boolean("")); // false
alert(Boolean("false")); // true -- gotcha! :)

This means:

var whatIWant = null || new ShinyObject(); // is a new shiny object
var whatIWant = undefined || "well defined"; // is "well defined"
var whatIWant = 0 || 42; // is 42
var whatIWant = "" || "a million bucks"; // is "a million bucks"
var whatIWant = "false" || "no way"; // is "false"

OwnerDisplayName: Ates Goral

ContentLicense: CC BY-SA 4.0

LastEditorDisplayName: Ates Goral


问:
Save an std::vector<int> to file
I noticed, that when using:

```<code>std::vector<int> v(100000);
...
std::ofstream outfile("outfile.dat", std::ios::out | std::ofstream::binary);
std::copy(v.begin(), v.end(), std::ostream_iterator<int>(outfile));
outfile.close();
</code>```

my std::vector<int> is not serialized as raw bytes data (4 bytes per int) but as string, i.e. the string representation of each integer is saved to disk, which I don't want.

How to save a std::vector<int> as binary data?

(Note: I'd like to learn it with standard C++03, before learning new methods for it).

答:
Might I recommend a saner way of doing it by using Protobufs? I will not type the code, but if you are working on a project, do not reinvent the wheel.

Using a protobuf would allow you to save the "type" of your data along with the data, and it would help you to extend your code with minimal fuss.

Title: Save an std::vector<int> to file

Body:
I noticed, that when using:

std::vector<int> v(100000);
...
std::ofstream outfile("outfile.dat", std::ios::out | std::ofstream::binary);
std::copy(v.begin(), v.end(), std::ostream_iterator<int>(outfile));
outfile.close();

my std::vector<int> is not serialized as raw bytes data (4 bytes per int) but as string, i.e. the string representation of each integer is saved to disk, which I don't want.

How to save a std::vector<int> as binary data?

(Note: I'd like to learn it with standard C++03, before learning new methods for it).