I can't create a toggle field without a db error

What’s the full query that’s triggering the error? And what version of EE?

Hi Robin,

it’s

SQLSTATE[HY000]: General error: 1364 Field 'group_id' doesn't have a default value: INSERT INTOexp_channel_fields(field_list_items,field_order,site_id,field_type,field_name,field_label,field_instructions,field_required,field_search,field_is_hidden,field_settings) VALUES ('', 5, 0, 'toggle', 'pulse_artist', 'Pulse Artist', '', 'n', 'n', 'n', 'YToxOntzOjE5OiJmaWVsZF9kZWZhdWx0X3ZhbHVlIjtzOjE6IjAiO30=')

EE 5.2.3

In the 4.0.0 update it should have modified any existing group_id and site_id fields to allow null:

ee()->smartforge->modify_column('channel_fields', array(
   'site_id' => array(
    'type'     => 'int',
    'unsigned' => TRUE,
    'null'     => TRUE,
   ),
   'group_id' => array(
    'type'     => 'int',
    'unsigned' => TRUE,
    'null'     => TRUE,
   ),
  ));

It looks to me like that didn’t happen, and that’s the problem. It’s easy enough to fix, but I don’t like the fact it happened. And maybe something else didn’t get updated as well. So- we need to keep an eye out.

But other than that- switch those both to allow null. If you do it via query, be sure to backup the table before hand and then

ALTER TABLE exp_channel_fields MODIFY group_id INT UNSIGNED

Make sense?

Thanks! That worked. But one follow up question:

Where does that data live in the db? I’m using Query module for this as it’s querying a large list. Would it not be in the same row as the rest of the channel entry? In my Field Manager, my new Toggle field is listed with an ID of #363, but the columns in the channel entries I’ve already been querying stop at 355.

I’ve been successfully using a SQL query for a few years that retrieves (for example): Name, Organization, Country

and now I’m trying to query: Name, Organization, Country, this new Toggle Field

does that live elsewhere and I’m going to have to do some sort of JOIN?

Ah, yes, the database structure changed and each field has its own table now. We left the existing fields where they are, but any new ones get their own table.

Look for exp_channel_data_field_x where x is the field id number.

Thanks Robin. I’m afraid my SQL chops aren’t all that. Was channel entry querying efficiency improved with this database change? I was using the Query module because using the Channel Entries tag was requiring a lot of memory. Is it worth trying with Channel Entries again or should I suck it up and figure out the proper SQL query? (i realize this is a hard question to answer)

I tested it and yeah, that’s a big ol’ query. It’s 600+ entries I need retrieved and displayed.

It just depends on what you’re doing- 600 is a lot. It’s not really the query that’s going to eat up overhead, it’s parsing it. The regular channel tag is parsing a lot.

But- here’s a cheat if you’re not good with queries. Go put the channel entry tag on an empty template- nothing else on it. Turn debugging on in ‘Settings- Debug and Output’. Then look at your template while logged in. You can view all of the queries. Grab the one pulling back your exp_channel_data_field_x and just simplify the stew out of it.

I think it’s just a left join to pull in an extra table. So look at the query and pull out the part you need and add it to what you’re already doing.

If you get stuck, just yell and I’ll take a look.

It’s pretty big. I feel like I could figure it out from there but I don’t understand all of the integers at the end. would i have to manually add a new integer to the query each time I created a new entry? (let’s see if this form will let me post this query in here):

SELECT t.entry_id, t.channel_id, t.forum_topic_id, t.author_id, t.ip_address, t.title, t.url_title, t.status, t.view_count_one, t.view_count_two, t.view_count_three, t.view_count_four, t.allow_comments, t.comment_expiration_date, t.sticky, t.entry_date, t.year, t.month, t.day, t.edit_date, t.expiration_date, t.recent_comment_date, t.comment_total, t.site_id as entry_site_id, w.channel_title, w.channel_name, w.channel_url, w.comment_url, w.comment_moderate, w.channel_html_formatting, w.channel_allow_img_urls, w.channel_auto_link_urls, w.comment_system_enabled, m.username, m.email, m.screen_name, m.signature, m.sig_img_filename, m.sig_img_width, m.sig_img_height, m.avatar_filename, m.avatar_width, m.avatar_height, m.photo_filename, m.photo_width, m.photo_height, m.group_id, m.member_id, wd.*, exp_channel_data_field_363.field_id_363, exp_channel_data_field_363.field_ft_363 FROM exp_channel_titles AS t LEFT JOIN exp_channels AS w ON t.channel_id = w.channel_id LEFT JOIN exp_channel_data AS wd ON t.entry_id = wd.entry_id LEFT JOIN exp_members AS m ON m.member_id = t.author_id LEFT JOIN exp_channel_data_field_363 ON t.entry_id = exp_channel_data_field_363.entry_id WHERE t.entry_id IN (2580,2907,2431,1112,1611,1114,1115,1635,1808,2648,2377,1117,1729,2238,4086,1144,2016,2498,1119,1146,2814,3896,2862,1151,1152,1154,1155,1156,2240,1157,1158,1159,1930,3393,1162,3756,2499,4028,3216,1167,1165,2969,2555,2880,3187,1127,1168,2855,1170,1172,1171,2103,1175,1176,3389,1177,2166,1128,1180,1182,2056,1183,2852,1185,2579,3645,2530,1193,1190,1666,1224,1590,3691,1196,2894,1199,3199,3887,2432,1728,2430,1202,1203,1204,1205,2665,1207,1208,1209,3268,3190,1210,1211,1213,2845,1214,1216,1218,3813,1219,1221,3184,1591,4143,1225,1227,3202,1228,3826,1229,1133,3384,2889,1610,1231,1232,3093,1233,1234,3387,1235,1236,1624,1450,2388,1243,1244,1245,2900,3274,2850,1249,1250,3592,1727,1246,2970,3888,1251,1253,1254,1255,1256,1257,1258,1129,1259,1260,1261,3381,3447,3189,1262,1264,1265,1267,1619,1268,1269,1623,3192,1271,1272,2164,1273,1274,2070,1275,1276,1277,2861,1278,1636,2019,1279,1280,1281,2307,2154,1282,1283,1284,2069,3188,3183,1285,1286,1287,1288,1289,3197,1291,2934,1293,2163,1296,4033,1297,1298,2885,1300,3186,1692,1301,1633,1302,2500,1475,1472,1473,1474,1476,3974,1477,1478,3273,3893,1481,1482,1483,1915,2608,3269,2214,1485,1938,1487,3194,1460,1461,1463,3897,1693,1464,1465,1540,2554,3195,2167,1467,2890,3267,2159,1469,3895,1516,1299,1593,1517,1518,2073,3388,1521,1522,1523,2529,1524,1526,1529,1531,1528,1665,1553,2844,1555,1561,1556,1557,1558,4076,2853,1563,1565,1566,1567,1569,3644,1577,1579,1580,1581,1630,1582,3201,1583,1586,1613,3591,1519,1515,1520,1525,1530,1527,1533,1534,3275,2211,1535,2909,2887,1618,3877,3104,2118,2057,2849,1620,1543,2607,1536,2126,1537,1538,1539,2168,2424,1541,1542,2156,1544,1545,1546,1547,3108,2832,3875,4087,1549,2685,1550,2898,1559,1560,1562,2856,2155,1568,2241,1570,4082,1609,1640,1572,1571,1573,1575,2893,1576,2162,3816,3696,1585,2296,2858,1304,4096,1649,1305,2891,1629,2910,3198,1641,1306,3270,1307,1308,1309,1310,2152,1632,1311,1312,2666,1313,1314,1315,1316,1317,4088,1318,1319,1320,1671,2294,1322,3680,1321,1660,3695,1617,3678,1323,3094,1324,1325,1642,3193,3382,1612,2138,2378,1784,1328,1329,2899,3185,1330,1332,1334,1335,1336,2895,2161,1337,1338,1339,1340,3894,1341,2649,2213,1342,2160,2897,1139,1344,1627,3891,1347,2072,1343,2068,2117,1348,2239,1349,1350,2433,1351,1352,3698,1354,4085,1355,1356,1357,1359,2847,1360,1979,1361,1916,2578,1364,1362,3890,1365,1366,1367,1621,2071,2502,2846,3191,2971,1368,1638,3386,1369,1650,1373,1374,1375,1371,3200,1748,3889,1376,1377,1672,1378,1996,2968,1637,1380,1215,2074,1382,1383,1384,2212,2831,1385,2857,1386,1387,2165,1388,1389,1615,3383,3827,1588,1392,1390,2664,4007,1663,1395,4094,1394,1396,1397,2510,1622,1399,1400,2417,2896,1393,1401,1628,2224,1402,2295,4072,3825,1403,1404,2833,1405,1407,3892,2651,1408,1410,1411,1412,3833,1413,3385,3694,1414,1658,1415,1416,4186,1634,1417,1418,3203,1419,1420,2375,3886,3390,1421,1423,3697,1424,1425,2309,1631,2133,1426,1614,1427,2711,2818,1430,2242,1431,1432,1434,2851,4071,2610,1514,2650,2848,2922,1435,1437,1438,1592,1442,1443,2888,2705,3196,3105,1444,1445,2694,1446,3981,3217,1447,2854,3181,3692,1449)ORDER BY FIELD(t.entry_id, 2580,2907,2431,1112,1611,1114,1115,1635,1808,2648,2377,1117,1729,2238,4086,1144,2016,2498,1119,1146,2814,3896,2862,1151,1152,1154,1155,1156,2240,1157,1158,1159,1930,3393,1162,3756,2499,4028,3216,1167,1165,2969,2555,2880,3187,1127,1168,2855,1170,1172,1171,2103,1175,1176,3389,1177,2166,1128,1180,1182,2056,1183,2852,1185,2579,3645,2530,1193,1190,1666,1224,1590,3691,1196,2894,1199,3199,3887,2432,1728,2430,1202,1203,1204,1205,2665,1207,1208,1209,3268,3190,1210,1211,1213,2845,1214,1216,1218,3813,1219,1221,3184,1591,4143,1225,1227,3202,1228,3826,1229,1133,3384,2889,1610,1231,1232,3093,1233,1234,3387,1235,1236,1624,1450,2388,1243,1244,1245,2900,3274,2850,1249,1250,3592,1727,1246,2970,3888,1251,1253,1254,1255,1256,1257,1258,1129,1259,1260,1261,3381,3447,3189,1262,1264,1265,1267,1619,1268,1269,1623,3192,1271,1272,2164,1273,1274,2070,1275,1276,1277,2861,1278,1636,2019,1279,1280,1281,2307,2154,1282,1283,1284,2069,3188,3183,1285,1286,1287,1288,1289,3197,1291,2934,1293,2163,1296,4033,1297,1298,2885,1300,3186,1692,1301,1633,1302,2500,1475,1472,1473,1474,1476,3974,1477,1478,3273,3893,1481,1482,1483,1915,2608,3269,2214,1

So the integers are the entry id numbers. Right before that query was another query where it got the entry ids- then it sticks them in there. You probably only want to do one query. So basically the ‘where’ part of the first query- or since you’re already doing it, the where part of you existing query.

Then something like:

SELECT t.entry_id, t.title, wd.field_id_x AS something_cool, exp_channel_data_field_363.field_id_363 AS some_other_cool_field
FROM exp_channel_titles AS t LEFT JOIN exp_channel_data AS wd ON t.entry_id = wd.entry_id 
LEFT JOIN exp_channel_data_field_363 ON t.entry_id = exp_channel_data_field_363.entry_id
WHERE some stuff

So you’re pulling in 3 tables- the exp_channel_titles table (t), the exp_channel_data table (wd) and that one field table exp_channel_data_field_336.

I put some aliases in there- that’s the AS some_other_cool_field- because it’s just easier to keep up with, especially with the query form.

You must need to fill in the ‘where’ bits and add any other fields you want to the select bit.

If anything doesn’t work, post me back what you’ve got so far. But I suspect you’re really close.

have you deal with it?