英文:
Is there a better/faster way to insert data to a database from an external api in Laravel?
问题
Here's the translation of the code-related portion of your text:
我目前正在从外部API获取数据，用于我的Laravel API。我已经让一切都运行起来了，但我觉得速度有点慢。
我使用 Http:get('url') 从API获取数据，速度很快。只有当我开始遍历数据并进行编辑时，速度才会变慢。
我不需要所有数据，但在将数据输入数据库之前进行编辑仍然很有用，以确保数据一致性。我还有一些列使用数据和一些逻辑来创建新列的列，以便每个应用/网站都不需要自行处理。
我在每次foreach循环中使用Eloquent的 Model::updateOrCreate() 方法保存到数据库，虽然这个json文件可能会很长，甚至可能超过6000行，所以循环遍历每个集合并修改值，然后每次保存到数据库显然需要时间。通常不会超过200个条目，但仍然需要时间。最终可能会更新为新的 upset() 方法，以减少对数据库的查询。在我的本地主机上，当前运行大约需要一分半钟的时间，这似乎太长了。
以下是我如何遍历数据的简化版本：
$json = json_decode($contents, true);
$features = $json['features'];
foreach ($features as $feature){
// 获取ID
$id = $feature['id'];
// 获取主要条件数据
$geometry = $feature['geometry'];
$properties = $feature['properties'];
// 获取次要几何数据
$geometryType = $geometry['type'];
$coordinates = $geometry['coordinates'];
Model::updateOrCreate(
[
'id' => $id,
],
[
'coordinates' => $coordinates,
'geometry_type' => $geometryType,
]);
}
大部分我在数据进入数据库之前对数据进行的工作都是清理一些文本字符串，但也有一些逻辑处理来规范化或准备数据供网站和应用使用。
有没有更高效的方法来达到相同的结果？最终，这将用于调度程序并定期运行。
关于API文档的示例数据结构：
[示例数据结构未提供翻译]
关于第二个相关问题：
既然这是在一定时间间隔内更新的，我让它从json数据中更新和创建记录，但是否有一种有效的方法来删除不再在json文件中的旧记录？我目前获取一个包含当前ID的数组，将其与新ID进行比较，然后循环遍历并删除它们。肯定有更好的方法。
请注意，如果您需要关于如何删除旧记录的更多信息，请提出具体的问题。
英文:
I am currently getting data from an external API for use my in Laravel API. I have everything working but I feel like it is slow.
I'm getting the data from the API with Http:get('url) and that works fast. It is only when I start looping through the data and making edits when things are slowing down.
I don't need all the data, but it would still be nice to edit before entering the data to the database as things aren't very consitent if possible. I also have a few columns that use data and some logic to make new columns so that each app/site doesn't need to do it.
I am saving to the database on each foreach loop with the eloquent Model:updateOrCreate() method which works but these json files can easily be 6000 lines long or more so it obviously takes time to loop through each set modify values and then save to the database each time. There usually isn't more than 200 or so entries but it still takes time. Will probably eventually update this to the new upset() method to make less queries to the database. Running in my localhost it is currently take about a minute and a half to run, which just seams way too long.
Here is a shortened version of how I was looping through the data.
$json = json_decode($contents, true);
$features = $json['features'];
foreach ($features as $feature){
// Get ID
$id = $feature['id'];
// Get primary condition data
$geometry = $feature['geometry'];
$properties = $feature['properties'];
// Get secondary geometry data
$geometryType = $geometry['type'];
$coordinates = $geometry['coordinates'];
Model::updateOrCreate(
[
'id' => $id,
],
[
'coordinates' => $coordinates,
'geometry_type' => $geometryType,
]);
}
Most of what I'm doing behind the scenes to the data before going into the database is cleaning up some text strings but there are a few logic things to normalize or prep the data for websites and apps.
Is there a more efficient way to get the same result? This will ultimately be used in a scheduler and run on an interval.
Example Data structure from API documentation
{
"$schema": "http://json-schema.org/draft-04/schema#",
"additionalProperties": false,
"properties": {
"features": {
"items": {
"additionalProperties": false,
"properties": {
"attributes": {
"type": [
"object",
"null"
]
},
"geometry": {
"additionalProperties": false,
"properties": {
"coordinates": {
"items": {
"items": {
"type": "number"
},
"type": "array"
},
"type": "array"
},
"type": {
"type": "string"
}
},
"required": [
"coordinates",
"type"
],
"type": "object"
},
"properties": {
"additionalProperties": false,
"properties": {
"currentConditions": {
"items": {
"properties": {
"additionalData": {
"type": "string"
},
"conditionDescription": {
"type": "string"
},
"conditionId": {
"type": "integer"
},
"confirmationTime": {
"type": "integer"
},
"confirmationUserName": {
"type": "string"
},
"endTime": {
"type": "integer"
},
"id": {
"type": "integer"
},
"sourceType": {
"type": "string"
},
"startTime": {
"type": "integer"
},
"updateTime": {
"type": "integer"
}
},
"required": [
"id",
"userName",
"updateTime",
"startTime",
"conditionId",
"conditionDescription",
"confirmationUserName",
"confirmationTime",
"sourceType",
"endTime"
],
"type": "object"
},
"type": "array"
},
"id": {
"type": "string"
},
"name": {
"type": "string"
},
"nameId": {
"type": "string"
},
"parentAreaId": {
"type": "integer"
},
"parentSubAreaId": {
"type": "integer"
},
"primaryLatitude": {
"type": "number"
},
"primaryLongitude": {
"type": "number"
},
"primaryMP": {
"type": "number"
},
"routeId": {
"type": "integer"
},
"routeName": {
"type": "string"
},
"routeSegmentIndex": {
"type": "integer"
},
"secondaryLatitude": {
"type": "number"
},
"secondaryLongitude": {
"type": "number"
},
"secondaryMP": {
"type": "number"
},
"sortOrder": {
"type": "integer"
}
},
"required": [
"id",
"name",
"nameId",
"routeId",
"routeName",
"primaryMP",
"secondaryMP",
"primaryLatitude",
"primaryLongitude",
"secondaryLatitude",
"secondaryLongitude",
"sortOrder",
"parentAreaId",
"parentSubAreaId",
"routeSegmentIndex",
"currentConditions"
],
"type": "object"
},
"type": {
"type": "string"
}
},
"required": [
"type",
"geometry",
"properties",
"attributes"
],
"type": "object"
},
"type": "array"
},
"type": {
"type": "string"
}
},
"required": [
"type",
"features"
],
"type": "object"
}
Second, related question.
Since this is being updated on an interval I have it updating and creating records from the json data, but is there an efficient way to delete old records that are no longer in the json file? I currently get an array of current ids and compare them to the new ids and then loop through each and delete them. There has to be a better way.
答案1
得分: 0
我认为你可以尝试这样做，关于第二个问题：
SomeModel::query()->whereNotIn('id', $newIds)->delete();
$newIds 可以在第一个循环中收集。
英文:
Have no idea what to say to your first question, but I think you may try to do something like this regarding the second question.
SomeModel::query()->whereNotIn('id', $newIds)->delete();
$newIds you can collect during the first loop.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论